# Daily Tenant Growth

Use this template when the user asks for a tenant's daily new and cumulative cluster trend, e.g. "Analyze tenant X's daily new cluster count and cumulative cluster count, aggregated by day." See SKILL.md for shared rules.

## Defaults

- **Window:** last 7 days, i.e. `DATE_SUB(CURRENT_DATE(), INTERVAL 6 DAY)` to `CURRENT_DATE()`. Override when the prompt names a range or relative window (30 / 60 days).
- **Service plan:** `Starter`, unless the prompt asks for `Essential`, `Premium`, or all TiDB X plans.

## Step 1 — Resolve tenant (fuzzy match)

When the user gives a tenant name or alias rather than an ID, list candidates first:

```sql
SELECT
  CAST(tenant_id AS STRING) AS tenant_id,
  ANY_VALUE(tenant_name) AS tenant_name,
  ANY_VALUE(tenant_alias) AS tenant_alias,
  ANY_VALUE(tenant_internal_user) AS tenant_internal_user,
  COUNT(*) AS active_starter_clusters
FROM `gcp-digital-bi.model_tidbcloud.dws_cloud_all_clusters_component_detail_basic_info`
WHERE cluster_service_plan = 'Starter'
  AND cluster_deleted_time IS NULL
  AND (
    LOWER(tenant_name) LIKE LOWER('%<tenant_name_from_user>%')
    OR LOWER(tenant_alias) LIKE LOWER('%<tenant_name_from_user>%')
  )
GROUP BY CAST(tenant_id AS STRING)
ORDER BY active_starter_clusters DESC;
```

If exactly one candidate matches, use its `tenant_id`. Otherwise list candidates and ask the user before proceeding.

## Step 2 — Daily new + cumulative series

Do **not** filter `clusters` by the report date window — the cumulative count needs all active clusters created on or before each report day.

```sql
WITH clusters AS (
  SELECT DATE(cluster_created_at) AS created_date
  FROM `gcp-digital-bi.model_tidbcloud.dws_cloud_all_clusters_component_detail_basic_info`
  WHERE cluster_service_plan = 'Starter'
    AND cluster_deleted_time IS NULL
    AND CAST(tenant_id AS STRING) = '<tenant_id_from_resolved_candidate>'
),
dates AS (
  SELECT day
  FROM UNNEST(GENERATE_DATE_ARRAY(
    DATE_SUB(CURRENT_DATE(), INTERVAL 6 DAY),
    CURRENT_DATE()
  )) AS day
)
SELECT
  day AS report_date,
  (SELECT COUNT(*) FROM clusters WHERE created_date = day) AS daily_new_count,
  (SELECT COUNT(*) FROM clusters WHERE created_date <= day) AS cumulative_cluster_count
FROM dates
ORDER BY report_date DESC;
```

Replace the `DATE_SUB(...)` / `CURRENT_DATE()` window with `DATE('<start_date>')`, `DATE('<end_date>')` when the prompt specifies dates.

## Optional — compact one-row summary

Use scalar subqueries (not joins between daily and cluster CTEs — those cause ambiguous column names) when the user wants a single-row overview:

```sql
WITH clusters AS (
  SELECT DATE(cluster_created_at) AS created_date
  FROM `gcp-digital-bi.model_tidbcloud.dws_cloud_all_clusters_component_detail_basic_info`
  WHERE cluster_service_plan = 'Starter'
    AND cluster_deleted_time IS NULL
    AND CAST(tenant_id AS STRING) = '<tenant_id_from_resolved_candidate>'
),
daily AS (
  SELECT created_date, COUNT(*) AS daily_new_count
  FROM clusters
  GROUP BY created_date
),
ranked AS (
  SELECT
    created_date,
    daily_new_count,
    ROW_NUMBER() OVER (ORDER BY daily_new_count DESC, created_date DESC) AS rn
  FROM daily
)
SELECT
  (SELECT MIN(created_date) FROM clusters) AS first_create_date,
  (SELECT MAX(created_date) FROM clusters) AS latest_create_date,
  (SELECT COUNT(*) FROM clusters) AS current_active_starter_clusters,
  (SELECT SUM(daily_new_count) FROM daily
   WHERE created_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 6 DAY)
     AND CURRENT_DATE()) AS new_clusters_last_7_days,
  (SELECT created_date FROM ranked WHERE rn = 1) AS peak_create_date,
  (SELECT daily_new_count FROM ranked WHERE rn = 1) AS peak_daily_new_count;
```