UIPackage
Menu

Framework

VueReactAngularSvelteWeb Components

Change language

GitHub repo

Sql Query Notebook

blockdevops
Boilerplate repo

Hex, Deepnote, and Jupyter style SQL analytical query notebook with documentation cells, executable Snowflake SQL editor with syntax coloring, live tabular results grid, CSV export, and chart visualization.

Installation

$npx uipkge-ng@latest add sql-query-notebook
Manifest:https://uipkge.dev/r/angular/sql-query-notebook.jsonInstalls to:src/app/components/blocks/sql-query-notebook/

Variants

Loading interactive previews…

Schema

Type aliases exported from this item's source. Use these to shape the data you pass in.

CohortRow
interface CohortRow {
  signup_cohort: string
  cohort_label: string
  total_customers: number
  avg_mrr: number
  cohort_ltv: number
  expansion_rate: string
  retention_score: number
}

npm dependencies

lucide-angular

Files installed (2)

  • src/app/components/blocks/sql-query-notebook/sql-query-notebook.component.ts32.4 kB
    // Hex, Deepnote, and Jupyter style SQL analytical query notebook with documentation
// cells, executable Snowflake SQL editor with syntax coloring, live tabular results grid,
// CSV export, and chart visualization. Port of the Vue block 1:1.
import { ChangeDetectionStrategy, Component, Input, computed, signal } from '@angular/core'
import {
  BarChart3,
  Check,
  Clock,
  Copy,
  Database,
  Download,
  FileCode2,
  FileText,
  Layers,
  Loader2,
  LucideAngularModule,
  Play,
  Plus,
  Table2,
  Target,
  Terminal,
  Trash2,
} from 'lucide-angular'
import { cn } from '@/lib/utils'
import { UiBadgeComponent } from '@/ui/badge/badge.component'
import { UiButtonComponent } from '@/ui/button/button.component'
import { UiCardComponent, UiCardContentComponent } from '@/ui/card/card.component'
import {
  UiTableBodyComponent,
  UiTableCellComponent,
  UiTableComponent,
  UiTableHeadComponent,
  UiTableHeaderComponent,
  UiTableRowComponent,
} from '@/ui/table/table.component'

interface CohortRow {
  signup_cohort: string
  cohort_label: string
  total_customers: number
  avg_mrr: number
  cohort_ltv: number
  expansion_rate: string
  retention_score: number
}

const sqlQuery = `SELECT
  DATE_TRUNC('month', created_at) AS signup_cohort,
  COUNT(DISTINCT customer_id) AS total_customers,
  ROUND(AVG(mrr), 2) AS avg_mrr,
  ROUND(SUM(lifetime_value), 2) AS cohort_ltv
FROM analytics.fct_subscriptions
WHERE created_at >= '2026-01-01'
GROUP BY 1 ORDER BY 1 DESC;`

const cohortResults: CohortRow[] = [
  {
    signup_cohort: '2026-06-01',
    cohort_label: 'Jun 2026',
    total_customers: 4820,
    avg_mrr: 248.5,
    cohort_ltv: 1428500.0,
    expansion_rate: '+18.4%',
    retention_score: 94,
  },
  {
    signup_cohort: '2026-05-01',
    cohort_label: 'May 2026',
    total_customers: 3940,
    avg_mrr: 232.1,
    cohort_ltv: 1148200.0,
    expansion_rate: '+14.1%',
    retention_score: 89,
  },
  {
    signup_cohort: '2026-04-01',
    cohort_label: 'Apr 2026',
    total_customers: 3110,
    avg_mrr: 219.8,
    cohort_ltv: 924100.0,
    expansion_rate: '+11.8%',
    retention_score: 85,
  },
  {
    signup_cohort: '2026-03-01',
    cohort_label: 'Mar 2026',
    total_customers: 2420,
    avg_mrr: 198.4,
    cohort_ltv: 682900.0,
    expansion_rate: '+8.6%',
    retention_score: 81,
  },
]

/** SQL syntax highlighter: tokenizes keywords, functions, strings, and numbers. */
export function highlightSqlLine(line: string): string {
  if (!line.trim()) return ' '
  const escaped = line.replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;')
  if (/^\s*--/.test(escaped)) return `<span class="text-zinc-500 italic">${escaped}</span>`
  // Every emitted <span> is parked behind a letter-only placeholder so later
  // passes cannot match inside the markup they already produced.
  const parked: string[] = []
  const park = (html: string) => {
    const key = String(parked.length)
      .split('')
      .map((d) => String.fromCharCode(97 + Number(d)))
      .join('')
    parked.push(html)
    return `\u0000${key}\u0000`
  }
  let out = escaped
  out = out.replace(/(['"])(?:(?=(\\?))\2.)*?\1/g, (m) => park(`<span class="text-success font-normal">${m}</span>`))
  out = out.replace(
    /\b(DATE_TRUNC|COUNT|DISTINCT|ROUND|AVG|SUM|MIN|MAX|COALESCE|CAST|CONCAT|NOW|CURRENT_TIMESTAMP)\b/g,
    (m) => park(`<span class="text-info font-semibold">${m}</span>`),
  )
  out = out.replace(
    /\b(SELECT|FROM|WHERE|GROUP BY|ORDER BY|GROUP|ORDER|BY|DESC|ASC|AS|AND|OR|NOT|IN|ON|JOIN|LEFT|RIGHT|INNER|OUTER|LIMIT|HAVING|UNION|ALL|CASE|WHEN|THEN|ELSE|END|WITH)\b/g,
    (m) => park(`<span class="text-chart-1 font-semibold">${m}</span>`),
  )
  out = out.replace(/\b(\d+(\.\d+)?)\b/g, (m) => park(`<span class="text-warning font-mono">${m}</span>`))
  return out.replace(/\u0000([a-j]+)\u0000/g, (_, key: string) => {
    const idx = Number(
      key
        .split('')
        .map((c: string) => String(c.charCodeAt(0) - 97))
        .join(''),
    )
    return parked[idx] ?? ''
  })
}

@Component({
  changeDetection: ChangeDetectionStrategy.Eager,
  selector: 'ui-sql-query-notebook, [ui-sql-query-notebook]',
  standalone: true,
  // Vue renders the root <div> itself: the host stays out of layout and `class` goes to the root.
  host: { '[attr.class]': '"contents"' },
  imports: [
    LucideAngularModule,
    UiBadgeComponent,
    UiButtonComponent,
    UiCardComponent,
    UiCardContentComponent,
    UiTableBodyComponent,
    UiTableCellComponent,
    UiTableComponent,
    UiTableHeadComponent,
    UiTableHeaderComponent,
    UiTableRowComponent,
  ],
  template: `
    <div data-slot="sql-query-notebook" [class]="rootClass">
      <!-- Top Notebook Header -->
      <header class="border-border bg-card/70 border-b px-4 py-3.5 sm:px-6">
        <div class="flex flex-col gap-3.5 lg:flex-row lg:items-center lg:justify-between">
          <!-- Notebook Identity & Metadata -->
          <div class="flex items-start gap-3">
            <div class="bg-primary/10 text-primary mt-0.5 flex size-9 shrink-0 items-center justify-center rounded-lg">
              <lucide-icon [img]="FileCode2" class="size-4.5" />
            </div>
            <div class="space-y-1">
              <div class="flex flex-wrap items-center gap-2">
                <h1 class="text-foreground text-sm font-semibold tracking-tight sm:text-base">
                  Customer Churn &amp; Cohort Analysis · Q3 2026
                </h1>
                <span ui-badge variant="secondary" class="font-mono text-xs">v2.4</span>
              </div>

              <div class="text-muted-foreground flex flex-wrap items-center gap-x-3 gap-y-1 text-xs">
                <div class="flex items-center gap-1.5">
                  <span class="relative flex size-2">
                    <span class="bg-success absolute inline-flex h-full w-full rounded-full opacity-75"></span>
                    <span class="bg-success relative inline-flex size-2 rounded-full"></span>
                  </span>
                  <span class="text-foreground/90 font-medium">Snowflake Production Warehouse</span>
                  <span class="text-muted-foreground">· Large Cluster</span>
                </div>
                <div class="flex items-center gap-1">
                  <lucide-icon [img]="Clock" class="size-3.5 opacity-70" />
                  <span>{{ lastSaved() }}</span>
                </div>
              </div>
            </div>
          </div>

          <!-- Global Action Controls -->
          <div class="flex flex-wrap items-center gap-2">
            <button
              ui-button
              variant="outline"
              size="sm"
              class="h-8 gap-1.5 text-xs shadow-none"
              title="Append SQL query cell"
              (click)="handleAddCell()"
            >
              <lucide-icon [img]="Plus" class="size-3.5" />
              <span>Add SQL Cell</span>
            </button>

            <button
              ui-button
              size="sm"
              class="h-8 gap-1.5 text-xs font-medium"
              [disabled]="isRunningAll()"
              (click)="handleRunAll()"
            >
              @if (isRunningAll()) {
                <lucide-icon [img]="Loader2" class="size-3.5 animate-spin" />
              } @else {
                <lucide-icon [img]="Play" class="size-3.5 fill-current" />
              }
              <span>{{ isRunningAll() ? 'Running Notebook...' : 'Run All Cells' }}</span>
              <kbd
                class="border-primary-foreground/30 bg-primary-foreground/10 hidden rounded border px-1 font-mono text-xs sm:inline"
              >
                ⌘↵
              </kbd>
            </button>
          </div>
        </div>
      </header>

      <!-- Notebook Content Stream Container -->
      <main class="space-y-4 p-4 sm:p-6">
        <!-- CELL 1: Markdown Documentation Cell -->
        <div ui-card [class]="docCardClass" (click)="activeCell.set('doc')">
          <div [class]="docStripClass"></div>

          <div class="border-border bg-muted/30 flex items-center justify-between border-b px-4 py-2">
            <div class="flex items-center gap-2">
              <span ui-badge variant="outline" class="font-mono text-xs font-semibold uppercase">[MD] Doc</span>
              <span class="text-muted-foreground text-xs">Methodology &amp; Hypothesis</span>
            </div>

            <button
              ui-button
              variant="ghost"
              size="sm"
              class="text-muted-foreground hover:text-foreground h-6 px-2 text-xs"
              (click)="$event.stopPropagation(); showMarkdownEditor.set(!showMarkdownEditor())"
            >
              <lucide-icon [img]="FileText" class="mr-1 size-3" />
              {{ showMarkdownEditor() ? 'Preview' : 'Source' }}
            </button>
          </div>

          <div ui-card-content class="p-4 sm:p-5">
            @if (showMarkdownEditor()) {
              <div class="space-y-2">
                <textarea
                  rows="3"
                  class="border-input bg-muted/20 text-foreground focus-visible:ring-ring w-full rounded-md border p-3 font-mono text-xs leading-relaxed outline-none focus-visible:ring-2"
                  [value]="markdownContent()"
                  (input)="markdownContent.set($any($event.target).value)"
                ></textarea>
              </div>
            } @else {
              <div class="space-y-3">
                <div class="border-border/60 border-b pb-2">
                  <h2 class="text-foreground text-base font-semibold tracking-tight">
                    Cohort Retention &amp; Expansion Query
                  </h2>
                  <p class="text-muted-foreground mt-1 text-xs leading-relaxed">
                    Grouping by monthly signup cohort to evaluate customer lifetime value (LTV) trajectory and expansion
                    MRR across enterprise tiers. Filtered for 2026 activations from the primary subscriptions warehouse.
                  </p>
                </div>

                <div class="flex flex-wrap items-center gap-2 pt-0.5">
                  <div
                    class="border-border bg-muted/40 text-muted-foreground flex flex-wrap items-center gap-1.5 rounded-md border px-2.5 py-1 text-xs"
                  >
                    <lucide-icon [img]="Database" class="text-foreground/70 size-3.5" />
                    <span>Source:</span>
                    <span class="text-foreground font-mono font-medium">analytics.fct_subscriptions</span>
                  </div>
                  <div
                    class="border-border bg-muted/40 text-muted-foreground flex flex-wrap items-center gap-1.5 rounded-md border px-2.5 py-1 text-xs"
                  >
                    <lucide-icon [img]="Target" class="text-warning size-3.5" />
                    <span>Target Retention:</span>
                    <span class="text-foreground font-medium">&gt; 118% Net Expansion</span>
                  </div>
                  <div
                    class="border-border bg-muted/40 text-muted-foreground flex flex-wrap items-center gap-1.5 rounded-md border px-2.5 py-1 text-xs"
                  >
                    <lucide-icon [img]="Layers" class="text-foreground/70 size-3.5" />
                    <span>Granularity:</span>
                    <span class="text-foreground font-mono font-medium">DATE_TRUNC('month')</span>
                  </div>
                </div>
              </div>
            }
          </div>
        </div>

        <!-- CELL 2: SQL Query Execution Cell -->
        <div ui-card [class]="sqlCardClass" (click)="activeCell.set('sql')">
          <div [class]="sqlStripClass"></div>

          <div class="border-border bg-muted/30 flex flex-wrap items-center justify-between gap-2 border-b px-4 py-2">
            <div class="flex flex-wrap items-center gap-2">
              <span
                ui-badge
                variant="default"
                class="bg-primary text-primary-foreground font-mono text-xs font-semibold"
              >
                [1] SQL
              </span>
              <span ui-badge variant="secondary" class="font-mono text-xs">Snowflake SQL</span>

              <div
                class="border-border/80 bg-background/80 flex items-center gap-1.5 rounded-md border px-2 py-0.5 text-xs"
              >
                <span [class]="statusDotClass"></span>
                <span class="text-muted-foreground font-mono">
                  {{ isCellRunning() ? 'Executing query...' : executionTime() + ' · ' + rowCountText() + ' rows' }}
                </span>
              </div>
            </div>

            <div class="flex items-center gap-1.5">
              <button
                ui-button
                variant="outline"
                size="sm"
                class="h-7 gap-1 px-2.5 text-xs shadow-none"
                title="Copy SQL Query"
                (click)="$event.stopPropagation(); handleCopySql()"
              >
                @if (copiedSql()) {
                  <lucide-icon [img]="Check" class="text-success size-3" />
                } @else {
                  <lucide-icon [img]="Copy" class="size-3" />
                }
                <span>{{ copiedSql() ? 'Copied' : 'Copy SQL' }}</span>
              </button>

              <button
                ui-button
                size="sm"
                class="h-7 gap-1 px-2.5 text-xs font-medium"
                [disabled]="isCellRunning()"
                (click)="$event.stopPropagation(); handleRunCell()"
              >
                @if (isCellRunning()) {
                  <lucide-icon [img]="Loader2" class="size-3 animate-spin" />
                } @else {
                  <lucide-icon [img]="Play" class="size-3 fill-current" />
                }
                <span>Run Cell</span>
              </button>
            </div>
          </div>

          <!-- Code Editor Body (Dark Theme) -->
          <div
            class="relative flex overflow-x-auto bg-zinc-950 py-3 font-mono text-xs leading-relaxed text-zinc-100 select-text"
          >
            <div class="flex flex-col border-r border-zinc-800/80 px-3 text-right text-zinc-600 select-none">
              @for (line of sqlLines(); track $index) {
                <span [class]="lineNumberClass($index + 1)">{{ $index + 1 }}</span>
              }
            </div>

            <div class="flex-1 px-4 whitespace-pre">
              @for (line of sqlLines(); track $index) {
                <div
                  [class]="sqlLineClass($index + 1)"
                  (mouseenter)="hoveredSqlLine.set($index + 1)"
                  (mouseleave)="hoveredSqlLine.set(null)"
                >
                  <span [innerHTML]="highlightLine(line)"></span>
                </div>
              }
            </div>
          </div>

          <!-- Tabular Query Results & Output Grid -->
          <div class="border-border border-t">
            <div class="border-border bg-card flex flex-wrap items-center justify-between gap-2 border-b px-4 py-2">
              <div class="flex items-center gap-2">
                <div class="flex items-center gap-1.5">
                  <lucide-icon [img]="Table2" class="text-primary size-4" />
                  <span class="text-foreground text-xs font-semibold">Query Output</span>
                </div>
                <span ui-badge variant="secondary" class="font-mono text-xs">4 cohorts · 14,290 records</span>
                <span class="text-muted-foreground hidden font-mono text-xs sm:inline">
                  Last run: {{ lastExecutionTimestamp() }}
                </span>
              </div>

              <div class="flex flex-wrap items-center gap-2">
                <div class="border-border bg-muted/40 flex items-center rounded-lg border p-0.5">
                  <button type="button" [class]="resultTabClass('table')" (click)="activeResultTab.set('table')">
                    <lucide-icon [img]="Table2" class="size-3.5" />
                    <span>Table</span>
                  </button>
                  <button type="button" [class]="resultTabClass('chart')" (click)="activeResultTab.set('chart')">
                    <lucide-icon [img]="BarChart3" class="size-3.5" />
                    <span>Chart</span>
                  </button>
                </div>

                <button
                  ui-button
                  aria-label="Download attachment"
                  variant="outline"
                  size="sm"
                  class="h-7 gap-1 px-2.5 text-xs shadow-none"
                  (click)="handleExportCsv()"
                >
                  @if (copiedCsv()) {
                    <lucide-icon [img]="Check" class="text-success size-3" />
                  } @else {
                    <lucide-icon [img]="Download" class="size-3" />
                  }
                  <span>{{ copiedCsv() ? 'Copied CSV!' : 'Export CSV' }}</span>
                </button>

                @if (activeResultTab() === 'table') {
                  <button
                    ui-button
                    variant="secondary"
                    size="sm"
                    class="h-7 gap-1 px-2.5 text-xs"
                    (click)="activeResultTab.set('chart')"
                  >
                    <lucide-icon [img]="BarChart3" class="size-3" />
                    <span>Visualize as Chart</span>
                  </button>
                }
              </div>
            </div>

            @if (activeResultTab() === 'table') {
              <div class="overflow-x-auto">
                <table ui-table>
                  <thead ui-table-header>
                    <tr ui-table-row class="bg-muted/40 hover:bg-muted/40">
                      <th ui-table-head class="text-xs font-semibold">signup_cohort</th>
                      <th ui-table-head class="text-right text-xs font-semibold">total_customers</th>
                      <th ui-table-head class="text-right text-xs font-semibold">avg_mrr</th>
                      <th ui-table-head class="text-right text-xs font-semibold">cohort_ltv</th>
                      <th ui-table-head class="text-right text-xs font-semibold">expansion_velocity</th>
                    </tr>
                  </thead>
                  <tbody ui-table-body>
                    @for (row of allCohorts; track row.signup_cohort; let idx = $index) {
                      <tr
                        ui-table-row
                        [class]="idx % 2 === 1 ? 'bg-muted/15 text-xs transition-colors' : 'text-xs transition-colors'"
                      >
                        <td ui-table-cell class="font-mono font-medium">
                          <div class="flex items-center gap-2">
                            <span class="text-muted-foreground font-mono text-xs">{{ idx + 1 }}</span>
                            <span class="text-foreground font-semibold">{{ row.signup_cohort }}</span>
                            <span ui-badge variant="secondary" class="font-sans text-xs font-normal">
                              {{ row.cohort_label }}
                            </span>
                          </div>
                        </td>
                        <td ui-table-cell class="text-foreground text-right font-mono font-medium tabular-nums">
                          {{ row.total_customers.toLocaleString() }}
                        </td>
                        <td ui-table-cell class="text-foreground text-right font-mono tabular-nums">
                          &#36;{{ row.avg_mrr.toFixed(2) }}
                        </td>
                        <td ui-table-cell class="text-foreground text-right font-mono font-semibold tabular-nums">
                          &#36;{{
                            row.cohort_ltv.toLocaleString('en-US', {
                              minimumFractionDigits: 2,
                              maximumFractionDigits: 2,
                            })
                          }}
                        </td>
                        <td ui-table-cell class="text-right">
                          <span
                            class="bg-success/10 text-success rounded-md px-2 py-0.5 font-mono text-xs font-semibold"
                          >
                            {{ row.expansion_rate }}
                          </span>
                        </td>
                      </tr>
                    }
                  </tbody>
                </table>

                <div
                  class="border-border bg-muted/20 flex flex-wrap items-center justify-between gap-3 border-t px-4 py-2.5 text-xs"
                >
                  <span class="text-muted-foreground font-mono">4 of 4 cohort partitions loaded · 0 errors</span>
                  <div class="flex flex-wrap items-center gap-4">
                    <span class="text-muted-foreground">
                      Total Active Accounts:
                      <strong class="text-foreground font-mono tabular-nums">{{
                        totalCustomersSum().toLocaleString()
                      }}</strong>
                    </span>
                    <span class="text-muted-foreground">
                      Cumulative Cohort LTV:
                      <strong class="text-foreground font-mono tabular-nums">
                        &#36;{{ totalLtvSum().toLocaleString('en-US', { minimumFractionDigits: 2 }) }}
                      </strong>
                    </span>
                  </div>
                </div>
              </div>
            } @else {
              <div class="space-y-5 p-4 sm:p-6">
                <div class="flex flex-col justify-between gap-2 sm:flex-row sm:items-center">
                  <div>
                    <h3 class="text-foreground text-sm font-semibold">Cohort Lifetime Value Trajectory</h3>
                    <p class="text-muted-foreground text-xs">
                      Progression of cumulative customer LTV and expansion momentum by activation month.
                    </p>
                  </div>
                  <span ui-badge variant="outline" class="font-mono text-xs">Snowflake Snowpark Analytics</span>
                </div>

                <div class="space-y-3.5">
                  @for (item of allCohorts; track item.signup_cohort) {
                    <div class="border-border/60 bg-card space-y-1.5 rounded-lg border p-3 shadow-none">
                      <div class="flex items-center justify-between text-xs">
                        <div class="flex items-center gap-2">
                          <span class="font-mono font-semibold">{{ item.cohort_label }}</span>
                          <span class="text-muted-foreground font-mono">({{ item.signup_cohort }})</span>
                          <span ui-badge variant="secondary" class="font-mono text-xs">
                            {{ item.total_customers.toLocaleString() }} customers
                          </span>
                        </div>
                        <div class="flex items-center gap-3">
                          <span class="text-muted-foreground font-mono"
                            >Avg MRR: &#36;{{ item.avg_mrr.toFixed(2) }}</span
                          >
                          <span class="text-foreground font-mono font-bold tabular-nums">
                            &#36;{{ item.cohort_ltv.toLocaleString('en-US', { minimumFractionDigits: 2 }) }}
                          </span>
                        </div>
                      </div>

                      <div class="bg-muted/60 relative h-3.5 w-full overflow-hidden rounded-full">
                        <div
                          class="bg-primary absolute top-0 bottom-0 left-0 rounded-full transition-colors duration-500"
                          [style.width.%]="(item.cohort_ltv / maxCohortLtv()) * 100"
                        ></div>
                      </div>
                    </div>
                  }
                </div>
              </div>
            }
          </div>
        </div>

        <!-- CELL 3: Downstream Python / Added Cell -->
        @if (showExtraCell()) {
          <div ui-card [class]="extraCardClass" (click)="activeCell.set('extra')">
            <div [class]="extraStripClass"></div>

            <div class="border-border bg-muted/30 flex items-center justify-between border-b px-4 py-2">
              <div class="flex items-center gap-2">
                <span ui-badge variant="secondary" class="font-mono text-xs font-semibold">[2] Python</span>
                <span class="text-muted-foreground text-xs">Downstream Snowpark Dataframe</span>
              </div>

              <div class="flex items-center gap-1.5">
                <button
                  ui-button
                  size="sm"
                  class="h-7 gap-1 px-2.5 text-xs"
                  [disabled]="extraCellRunning()"
                  (click)="$event.stopPropagation(); handleRunExtraCell()"
                >
                  @if (extraCellRunning()) {
                    <lucide-icon [img]="Loader2" class="size-3 animate-spin" />
                  } @else {
                    <lucide-icon [img]="Play" class="size-3 fill-current" />
                  }
                  <span>Run</span>
                </button>
                <button
                  ui-button
                  variant="ghost"
                  size="sm"
                  class="text-muted-foreground hover:text-destructive h-7 px-2"
                  aria-label="Delete query cell"
                  (click)="$event.stopPropagation(); showExtraCell.set(false)"
                >
                  <lucide-icon [img]="Trash2" class="size-3" />
                </button>
              </div>
            </div>

            <div class="bg-zinc-950 p-4 font-mono text-xs leading-relaxed text-zinc-300">
              <pre
                class="text-zinc-400"
              ><code><span class="text-chart-1">import</span> snowflake.snowpark <span class="text-chart-1">as</span> snowpark
<span class="text-chart-1">import</span> polars <span class="text-chart-1">as</span> pl

<span class="text-zinc-500"># Read Cell [1] tabular results and compute hazard rate</span>
df = cell_1_results.to_pandas()
df[<span class="text-success">'expansion_velocity'</span>] = df[<span class="text-success">'cohort_ltv'</span>] / df[<span class="text-success">'total_customers'</span>]
<span class="text-info">print</span>(f<span class="text-success">"Average cohort customer value: &#36;&#123;df['expansion_velocity'].mean():.2f&#125;"</span>)</code></pre>
            </div>

            <div class="border-border bg-muted/20 border-t p-3 font-mono text-xs">
              <div class="text-muted-foreground flex items-center gap-2">
                <lucide-icon [img]="Terminal" class="text-success size-3.5" />
                <span>Output:</span>
                <span class="text-success font-semibold">Average cohort customer value: $292.77</span>
              </div>
            </div>
          </div>
        }
      </main>
    </div>
  `,
})
export class UiSqlQueryNotebookComponent {
  protected readonly BarChart3 = BarChart3
  protected readonly Check = Check
  protected readonly Clock = Clock
  protected readonly Copy = Copy
  protected readonly Database = Database
  protected readonly Download = Download
  protected readonly FileCode2 = FileCode2
  protected readonly FileText = FileText
  protected readonly Layers = Layers
  protected readonly Loader2 = Loader2
  protected readonly Play = Play
  protected readonly Plus = Plus
  protected readonly Table2 = Table2
  protected readonly Target = Target
  protected readonly Terminal = Terminal
  protected readonly Trash2 = Trash2
  protected readonly allCohorts = cohortResults

  @Input('class') className?: string

  readonly isRunningAll = signal(false)
  readonly isCellRunning = signal(false)
  readonly lastSaved = signal('Autosaved 1m ago')
  readonly activeCell = signal<'doc' | 'sql' | 'extra'>('sql')
  readonly activeResultTab = signal<'table' | 'chart'>('table')
  readonly copiedSql = signal(false)
  readonly copiedCsv = signal(false)
  readonly hoveredSqlLine = signal<number | null>(null)
  readonly executionTime = signal('1.24s')
  readonly rowCount = signal(14290)
  readonly lastExecutionTimestamp = signal('14:28:40')
  readonly showMarkdownEditor = signal(false)
  readonly showExtraCell = signal(false)
  readonly extraCellRunning = signal(false)
  readonly markdownContent = signal(
    '# Cohort Retention & Expansion Query: Grouping by monthly signup cohort to evaluate customer lifetime value (LTV) trajectory and expansion MRR across enterprise tiers. Filtered for 2026 activations from the primary subscriptions warehouse.',
  )

  readonly sqlLines = computed(() => sqlQuery.split('\n'))
  readonly totalCustomersSum = computed(() => cohortResults.reduce((acc, r) => acc + r.total_customers, 0))
  readonly totalLtvSum = computed(() => cohortResults.reduce((acc, r) => acc + r.cohort_ltv, 0))
  readonly maxCohortLtv = computed(() => Math.max(...cohortResults.map((r) => r.cohort_ltv)))
  readonly rowCountText = computed(() => this.rowCount().toLocaleString())

  get rootClass(): string {
    return cn(
      'bg-background text-foreground border-border w-full overflow-hidden rounded-xl border shadow-xs',
      this.className,
    )
  }

  get statusDotClass(): string {
    return cn(
      'size-1.5 rounded-full transition-colors',
      this.isCellRunning() ? 'bg-warning animate-pulse' : 'bg-success',
    )
  }

  get docCardClass(): string {
    return cn(
      'border-border relative overflow-hidden shadow-none transition-colors',
      this.activeCell() === 'doc' ? 'ring-primary/20 ring-2' : '',
    )
  }

  get docStripClass(): string {
    return cn(
      'absolute top-0 bottom-0 left-0 w-1 transition-colors',
      this.activeCell() === 'doc' ? 'bg-primary' : 'bg-transparent',
    )
  }

  get sqlCardClass(): string {
    return cn(
      'border-border relative overflow-hidden shadow-none transition-colors',
      this.activeCell() === 'sql' ? 'ring-primary/20 ring-2' : '',
    )
  }

  get sqlStripClass(): string {
    return cn(
      'absolute top-0 bottom-0 left-0 w-1 transition-colors',
      this.activeCell() === 'sql' ? 'bg-primary' : 'bg-transparent',
    )
  }

  get extraCardClass(): string {
    return cn(
      'border-border relative overflow-hidden shadow-none transition-colors',
      this.activeCell() === 'extra' ? 'ring-primary/20 ring-2' : '',
    )
  }

  get extraStripClass(): string {
    return cn(
      'absolute top-0 bottom-0 left-0 w-1 transition-colors',
      this.activeCell() === 'extra' ? 'bg-primary' : 'bg-transparent',
    )
  }

  lineNumberClass(idx: number): string {
    return cn('h-5 leading-5 transition-colors', this.hoveredSqlLine() === idx ? 'font-semibold text-zinc-300' : '')
  }

  sqlLineClass(idx: number): string {
    return cn(
      'group flex h-5 items-center rounded-xs px-1 leading-5 transition-colors',
      this.hoveredSqlLine() === idx ? 'bg-zinc-800/40' : '',
    )
  }

  resultTabClass(tab: 'table' | 'chart'): string {
    return cn(
      'flex items-center gap-1.5 rounded-md px-2.5 py-1 text-xs font-medium transition-colors',
      this.activeResultTab() === tab
        ? 'bg-background text-foreground shadow-xs'
        : 'text-muted-foreground hover:text-foreground',
    )
  }

  highlightLine(line: string): string {
    return highlightSqlLine(line)
  }

  handleRunAll(): void {
    if (this.isRunningAll()) return
    this.isRunningAll.set(true)
    this.isCellRunning.set(true)
    setTimeout(() => {
      this.isCellRunning.set(false)
      this.isRunningAll.set(false)
      this.lastExecutionTimestamp.set(new Date().toTimeString().split(' ')[0])
      this.lastSaved.set('Saved just now')
    }, 620)
  }

  handleRunCell(): void {
    if (this.isCellRunning()) return
    this.isCellRunning.set(true)
    setTimeout(() => {
      this.isCellRunning.set(false)
      this.lastExecutionTimestamp.set(new Date().toTimeString().split(' ')[0])
    }, 480)
  }

  async handleCopySql(): Promise<void> {
    try {
      await navigator.clipboard.writeText(sqlQuery)
      this.copiedSql.set(true)
      setTimeout(() => this.copiedSql.set(false), 2000)
    } catch {
      // Clipboard unavailable
    }
  }

  async handleExportCsv(): Promise<void> {
    const csvHeaders = 'signup_cohort,total_customers,avg_mrr,cohort_ltv\n'
    const csvRows = cohortResults
      .map((r) => `${r.signup_cohort},${r.total_customers},${r.avg_mrr},${r.cohort_ltv}`)
      .join('\n')
    try {
      await navigator.clipboard.writeText(csvHeaders + csvRows)
      this.copiedCsv.set(true)
      setTimeout(() => this.copiedCsv.set(false), 2000)
    } catch {
      // Clipboard unavailable
    }
  }

  handleAddCell(): void {
    this.showExtraCell.set(true)
    this.activeCell.set('extra')
  }

  handleRunExtraCell(): void {
    this.extraCellRunning.set(true)
    setTimeout(() => this.extraCellRunning.set(false), 450)
  }
}
  • src/app/components/blocks/sql-query-notebook/index.ts0.1 kB

Raw manifest:https://uipkge.dev/r/angular/sql-query-notebook.json