SQL for Analysts

SELECT event_at::date AS day,
       COUNT(DISTINCT user_id) AS dau
FROM events
WHERE event_at >= '2026-03-01'
  AND event_at <  '2026-03-08'
GROUP BY 1
ORDER BY 1;

SELECT
  COUNT(DISTINCT user_id)                                    AS visitors,
  COUNT(DISTINCT user_id) FILTER (WHERE event='add_to_cart') AS carted,
  COUNT(DISTINCT user_id) FILTER (WHERE event='purchase')    AS purchased,
  ROUND(100.0 * COUNT(DISTINCT user_id) FILTER (WHERE event='purchase')
              / NULLIF(COUNT(DISTINCT user_id), 0), 2)       AS cr_pct
FROM events;

WITH daily AS (
  SELECT event_at::date AS day, COUNT(DISTINCT user_id) AS dau
  FROM events
  WHERE event_at >= '2026-03-01' AND event_at < '2026-04-01'
  GROUP BY 1
),
monthly AS (
  SELECT COUNT(DISTINCT user_id) AS mau
  FROM events
  WHERE event_at >= '2026-03-01' AND event_at < '2026-04-01'
)
SELECT ROUND(AVG(daily.dau), 1)                    AS avg_dau,
       monthly.mau,
       ROUND(100.0 * AVG(daily.dau) / monthly.mau, 1) AS sticky_pct
FROM daily CROSS JOIN monthly
GROUP BY monthly.mau;

SELECT created_at::date AS day,
       SUM(amount)                                    AS daily,
       SUM(SUM(amount)) OVER (ORDER BY created_at::date) AS running_total
FROM orders
WHERE status = 'paid'
  AND created_at < '2026-03-08'
GROUP BY 1
ORDER BY 1;

WITH spend AS (
  SELECT date_trunc('month', u.signup_date)::date AS cohort_month,
         o.created_at::date - u.signup_date       AS day_no,
         o.amount
  FROM users u
  JOIN orders o ON o.user_id = u.user_id AND o.status = 'paid'
  WHERE o.created_at::date >= u.signup_date
)
SELECT cohort_month,
       SUM(amount) FILTER (WHERE day_no <= 7) AS revenue_day_7,
       SUM(amount)                            AS revenue_total
FROM spend
GROUP BY 1
ORDER BY 1;

SELECT
  COUNT(*) FILTER (
      WHERE started_on < '2026-03-01'
        AND (ended_on IS NULL OR ended_on >= '2026-03-01')
  ) AS active_at_start,
  COUNT(*) FILTER (
      WHERE ended_on >= '2026-03-01' AND ended_on < '2026-04-01'
  ) AS churned,
  ROUND(100.0
      * COUNT(*) FILTER (WHERE ended_on >= '2026-03-01' AND ended_on < '2026-04-01')
      / NULLIF(COUNT(*) FILTER (
          WHERE started_on < '2026-03-01'
            AND (ended_on IS NULL OR ended_on >= '2026-03-01')), 0)
    , 1) AS churn_pct
FROM subscriptions;

WITH first_day AS (
  SELECT user_id, MIN(event_at::date) AS d0
  FROM events
  GROUP BY 1
)
SELECT COUNT(*)                                   AS users,
       COUNT(*) FILTER (WHERE returned)           AS retained_d7,
       ROUND(100.0 * COUNT(*) FILTER (WHERE returned) / COUNT(*), 1) AS retention_pct
FROM (
  SELECT f.user_id,
         EXISTS (
           SELECT 1 FROM events e
           WHERE e.user_id = f.user_id
             AND e.event_at::date = f.d0 + 7
         ) AS returned
  FROM first_day f
) t;

WITH gaps AS (
  SELECT user_id, event_at, event,
         LAG(event_at) OVER (PARTITION BY user_id ORDER BY event_at) AS prev_at
  FROM events
  WHERE user_id IN (1, 2)
),
flags AS (
  SELECT *,
         CASE WHEN prev_at IS NULL
                OR event_at - prev_at > INTERVAL '30 minutes'
              THEN 1 ELSE 0 END AS is_new
  FROM gaps
)
SELECT user_id, event_at, event,
       SUM(is_new) OVER (PARTITION BY user_id ORDER BY event_at
                         ROWS UNBOUNDED PRECEDING) AS session_no
FROM flags
ORDER BY user_id, event_at
LIMIT 8;

WITH revenue AS (
  SELECT u.country, u.user_id, SUM(o.amount) AS revenue
  FROM users u
  JOIN orders o ON o.user_id = u.user_id AND o.status = 'paid'
  WHERE u.country IS NOT NULL
  GROUP BY 1, 2
)
SELECT country, user_id, revenue, rn
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY country ORDER BY revenue DESC) AS rn
  FROM revenue
) ranked
WHERE rn <= 3
ORDER BY country, rn;

SELECT COUNT(*)                  AS rows_after_join,
       COUNT(o.order_id)         AS orders,
       COUNT(DISTINCT u.user_id) AS users
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id;

SELECT
  ROUND(SUM(o.amount) / COUNT(DISTINCT u.user_id), 2) AS arpu,
  ROUND(SUM(o.amount) / COUNT(DISTINCT o.user_id), 2) AS arppu,
  COUNT(DISTINCT u.user_id)                           AS all_users,
  COUNT(DISTINCT o.user_id)                           AS paying_users
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id AND o.status = 'paid';

SELECT COUNT(*)                             AS total,
       COUNT(country)                       AS with_country,
       COUNT(*) - COUNT(country)            AS null_country,
       COUNT(*) FILTER (WHERE plan IS NULL) AS null_plan
FROM users;

SELECT
  (SELECT COUNT(*) FROM users
   WHERE country NOT IN (SELECT country FROM users
                         WHERE country IS NULL OR country = 'KZ')) AS with_null_in_list,
  (SELECT COUNT(*) FROM users
   WHERE country NOT IN (SELECT country FROM users
                         WHERE country = 'KZ'))                    AS without_null,
  (SELECT COUNT(*) FROM users u
   WHERE NOT EXISTS (SELECT 1 FROM users x
                     WHERE x.country = 'KZ' AND x.country = u.country)) AS not_exists_ok;

SELECT u.user_id,
       u.signup_date,
       MIN(o.created_at)::date                     AS first_order,
       MIN(o.created_at)::date - u.signup_date     AS days_to_first_order
FROM users u
JOIN orders o ON o.user_id = u.user_id AND o.status = 'paid'
GROUP BY 1, 2
HAVING MIN(o.created_at)::date >= u.signup_date
ORDER BY days_to_first_order
LIMIT 5;

SELECT date_trunc('week', event_at)::date AS week_start,
       COUNT(DISTINCT user_id)             AS wau
FROM events
GROUP BY 1
ORDER BY 1;