# Reusable SQL Patterns

Battle-tested CTEs and queries for dashboards and analysis.

## CTE instancias_reales

**ALWAYS include** in dashboards measuring adoption by client. Excludes internal and test instances.

### Why simple filters are not enough

- `Instances."isTest"` → almost nobody sets it, not reliable
- `InstanceCrmData.status = 'PRODUCTION'` → leaves out trials/onboardings that ARE real clients

### Heuristic

An instance is **real** if it meets ALL these conditions:

1. `i."deletedAt" IS NULL`
2. Name does NOT match test/internal keywords
3. Has >= 10 non-deleted users
4. Less than 30% of users have `@humand.co` email

### Snippet

Paste at the beginning of the query. Filter with `u."instanceId" IN (SELECT id FROM instancias_reales)`:

```sql
WITH instancias_con_actividad AS (
  -- ADAPT: change FROM/WHERE to the dashboard's domain
  SELECT DISTINCT u."instanceId" AS id
  FROM "[RelevantTable]" t
  JOIN "Users" u ON u.id = t."userId"
  WHERE t."[dateField]" >= '{{ since }}'::date
),
instancias_reales AS (
  SELECT i.id
  FROM "Instances" i
  JOIN instancias_con_actividad ica ON ica.id = i.id
  LEFT JOIN "Users" u ON u."instanceId" = i.id AND u."deletedAt" IS NULL
  WHERE i."deletedAt" IS NULL
    AND LOWER(i.name) !~ '(test|testing|stock|sandbox|demo|prueba|ukulele|pixar|hive|talent[ ]?view|customer experience|nogueira|carla hu|beta.*tester|humand|sales)'
  GROUP BY i.id
  HAVING COUNT(u.id) >= 10
     AND COUNT(u.id) FILTER (WHERE u.email ILIKE '%@humand.co')::float
         / NULLIF(COUNT(u.id), 0) < 0.30
)
```

**Adapt `instancias_con_actividad`** to the dashboard's domain: if the feature is measured on another table (e.g. `Forms`, `Posts`), change the `FROM` to list only instances with events in the period. Reduces from ~3000 instances to ~500.

### Instances that typically get filtered out

- **By name**: Beta&testers, Customer Experience Team, Hive, Pixar, UkuleleLand, TalentView, Stock Carla Hu, Facundo Nogueira Stock
- **By high % humand**: OXXO tute, Talleres de Córdoba (Lucardo), Peusso
- **By few users**: Alfacom HAC, Networks Ventures

If a real client gets filtered (small SMB), adjust the >= 10 threshold or add an exception.

## Weekly trend

Temporal evolution of a metric, grouped by week:

```sql
SELECT
  DATE_TRUNC('week', t."[dateField]")::date AS week,
  COUNT(*) AS total,
  COUNT([feature_condition]) AS with_feature,
  ROUND(100.0 * COUNT([feature_condition]) / NULLIF(COUNT(*), 0), 1) AS pct
FROM "[Table]" t
JOIN "Users" u ON u.id = t."userId"
WHERE t."[dateField]" >= '{{ since }}'::date
  AND u."instanceId" IN (SELECT id FROM instancias_reales)
GROUP BY week
ORDER BY week
```

## New client cohort

How many clients used a feature for the first time each week + cumulative:

```sql
WITH first_use AS (
  SELECT u."instanceId", MIN(t."[dateField]") AS first_time
  FROM "[Table]" t
  JOIN "Users" u ON u.id = t."userId"
  WHERE [feature_condition]
    AND u."instanceId" IN (SELECT id FROM instancias_reales)
  GROUP BY u."instanceId"
),
per_week AS (
  SELECT DATE_TRUNC('week', first_time)::date AS week,
         COUNT(*) AS new_clients
  FROM first_use
  WHERE first_time >= '{{ since }}'::date
  GROUP BY week
)
SELECT week, new_clients,
       SUM(new_clients) OVER (ORDER BY week) AS cumulative
FROM per_week ORDER BY week
```

## User segmentation history

Consolidated summary with current state per group/item:

```sql
WITH changes AS (
  SELECT
    sg2.name AS group_name, si.name AS item,
    MIN(sh."createdAt") AS first_assignment,
    MAX(sh."updatedAt") AS last_update,
    MAX(sh."deletedAt") AS last_removal,
    COUNT(*) AS total_records,
    COUNT(sh."deletedAt") AS times_removed,
    BOOL_OR(sh."deletedAt" IS NULL) AS has_active
  FROM "SegmentationHistories" sh
  LEFT JOIN "Segmentations" s ON s.id = sh."segmentationsId"
  LEFT JOIN "SegmentationGroups" sg2 ON sg2.id = s."groupId"
  LEFT JOIN "SegmentationItems" si ON si.id = sh."itemId"
  WHERE sh."segmentableId" = :userId
    AND sh."segmentableType" = 'user'
    AND sh."instanceId" = :instanceId
  GROUP BY sg2.name, si.name
)
SELECT group_name, item, first_assignment, last_update,
  last_removal, times_removed,
  CASE WHEN has_active THEN 'ACTIVE' ELSE 'REMOVED' END AS current_state
FROM changes ORDER BY group_name, item
```

## General tips

- In `api_events` (DS 13): `user_id` is the affected user, `actor_id` is who performed the action
- `{{ since }}` parameter in Redash dashboards: use `'{{ since }}'::date` to cast
- Performance: always restrict with an "instances with activity" CTE before heavy joins
