Analytics SQL and debugging

Assume a warehouse table events(user_id, event, ts, ...) and users(user_id, created_at, ...). Dialects vary; the shapes matter more than vendor SQL. Tools change; the questions do not.

Product analytics without billing context answers "what did they do?" but not "was it worth it?" Join events to invoices or subscriptions on account_id for activation quality (paid within 30 days), a feature ROI sketch (not causal — see A/B testing) and support load per 1,000 actives after a release.

The big idea

DAU / WAU / MAU — count distinct users on your "active" event. Stickiness heuristic: DAU/MAU for the same month. A consumer social app might sit ~0.5; a weekly B2B tool might sit ~0.2 and still be healthy — compare to your history, not a universal benchmark.

Windowed funnels need time bounds; open-ended "ever converted" overstates recent cohorts. For how Postgres executes these joins and aggregates, see query execution.

When a dashboard "looks wrong," work a checklist before rewriting queries:

1. Definition — is everyone using the same event and window?
2. Deploy — did an SDK or schema change land that day?
3. Identity — did merge break, duplicating or dropping users?
4. Mix — did a campaign change traffic composition (Simpson)?
5. Bots / internals — filter employees, QA, load tests.
6. Timezone — UTC day boundaries vs local marketing calendars.

Write the corrected definition back into the tracking plan so the same scare does not recur.

A thin tracking plan in PostHog beats a perfect snowflake schema six months late.

Walking through it

Sketch, not a full warehouse model. Daily active users:

SELECT DATE(ts) AS day, COUNT(DISTINCT user_id) AS dau
FROM events
WHERE event = 'app_opened'
  AND ts >= DATE '2026-01-01'
GROUP BY 1
ORDER BY 1;

Windowed funnel (signup → activate in 7 days → paid in 30):

WITH firsts AS (
  SELECT user_id,
         MIN(CASE WHEN event = 'signup_completed' THEN ts END) AS t_signup,
         MIN(CASE WHEN event = 'project_created'  THEN ts END) AS t_activate,
         MIN(CASE WHEN event = 'subscription_started' THEN ts END) AS t_paid
  FROM events GROUP BY 1
)
SELECT
  COUNT(*) FILTER (WHERE t_signup IS NOT NULL) AS signed_up,
  COUNT(*) FILTER (WHERE t_activate IS NOT NULL
    AND t_activate <= t_signup + INTERVAL '7 days') AS activated_7d,
  COUNT(*) FILTER (WHERE t_paid IS NOT NULL
    AND t_paid <= t_signup + INTERVAL '30 days') AS paid_30d
FROM firsts WHERE t_signup IS NOT NULL;

Common mistakes

• Rewriting the query first when the SDK broke or identity merge failed.
• Ever-converted funnels for this week's signups.
• Joining to revenue in the product UI instead of the warehouse, then arguing about two numbers.

Check yourself

1. DAU dropped the morning after an SDK release. What do you check before a "growth emergency" meeting?

Definition, deploy, identity merge, then mix. A 50% drop in page_viewed is usually a broken SDK.

2. Why does the funnel SQL bound t_activate to 7 days after signup?

Without a window, "ever activated" overstates recent cohorts and mixes onboarding with months of drips.

Key takeaways

• Learn a few SQL shapes (DAU, windowed funnel, cohort); tools change, the questions do not.
• Debug definition, deploy, identity and mix before rewriting SQL.
• Join to money in the warehouse; keep PII out of the event stream.

Further learning

Mode's SQL tutorial is still one of the best free paths from SELECT to analytical joins.

Mode SQL TutorialLearn to answer questions with data using SQL, no prior coding required, guiding you through foundational to advanced SQL skills.ThoughtSpot

Back to the map: product analytics.