Snippet Library

1
CREATE TABLE users (
2
  id INTEGER PRIMARY KEY,
3
  email TEXT UNIQUE NOT NULL,
4
  name TEXT,
5
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
6
);

1
INSERT INTO users (id, email, name)
2
VALUES (1, 'ada@example.com', 'Ada')
3
ON CONFLICT(id) DO UPDATE SET
4
  email = excluded.email,
5
  name = excluded.name;

1
SELECT
2
  user_id,
3
  cohort,
4
  score,
5
  RANK() OVER (PARTITION BY cohort ORDER BY score DESC) AS cohort_rank
6
FROM leaderboard;

1
SELECT
2
  user_id,
3
  SUM(CASE WHEN event = 'signup' THEN 1 ELSE 0 END) AS signups,
4
  SUM(CASE WHEN event = 'purchase' THEN 1 ELSE 0 END) AS purchases
5
FROM events
6
GROUP BY user_id;

 1
WITH RECURSIVE tree AS (
 2
  SELECT id, parent_id, name, 0 AS depth
 3
  FROM categories
 4
  WHERE parent_id IS NULL
 5
  UNION ALL
 6
  SELECT c.id, c.parent_id, c.name, depth + 1
 7
  FROM categories c
 8
  JOIN tree t ON c.parent_id = t.id
 9
)
10
SELECT * FROM tree;

1
SELECT * FROM (
2
  SELECT
3
    user_id,
4
    cohort,
5
    score,
6
    ROW_NUMBER() OVER (PARTITION BY cohort ORDER BY score DESC) AS rnk
7
  FROM leaderboard
8
) ranked
9
WHERE rnk <= 3;

1
SELECT email, COUNT(*) AS occurrences
2
FROM users
3
GROUP BY email
4
HAVING COUNT(*) > 1;

1
SELECT
2
  json_extract(payload, '$.user.id') AS user_id,
3
  json_extract(payload, '$.event') AS event
4
FROM audit_log;

1
SELECT
2
  event_date,
3
  revenue,
4
  SUM(revenue) OVER (ORDER BY event_date ROWS UNBOUNDED PRECEDING) AS running_total
5
FROM daily_revenue;

1
SELECT
2
  event_date,
3
  metric_value,
4
  AVG(metric_value) OVER (
5
    ORDER BY event_date
6
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
7
  ) AS moving_avg_7d
8
FROM daily_metrics;

1
SELECT
2
  week_start,
3
  revenue,
4
  LAG(revenue, 1) OVER (ORDER BY week_start) AS prev_week_revenue,
5
  ROUND(
6
    100.0 * (revenue - LAG(revenue, 1) OVER (ORDER BY week_start))
7
    / NULLIF(LAG(revenue, 1) OVER (ORDER BY week_start), 0), 2
8
  ) AS pct_change
9
FROM weekly_revenue;

1
SELECT *
2
FROM events
3
WHERE created_at >= DATE('now', '-30 days')
4
  AND created_at < DATE('now');

 1
WITH cohorts AS (
 2
  SELECT
 3
    user_id,
 4
    DATE_TRUNC('month', first_seen) AS cohort_month
 5
  FROM users
 6
),
 7
activity AS (
 8
  SELECT
 9
    user_id,
10
    DATE_TRUNC('month', event_date) AS activity_month
11
  FROM user_events
12
)
13
SELECT
14
  c.cohort_month,
15
  a.activity_month,
16
  COUNT(DISTINCT c.user_id) AS retained_users,
17
  DATEDIFF('month', c.cohort_month, a.activity_month) AS months_since_signup
18
FROM cohorts c
19
JOIN activity a ON c.user_id = a.user_id
20
GROUP BY 1, 2
21
ORDER BY 1, 4;

1
SELECT
2
  user_id,
3
  revenue,
4
  NTILE(4) OVER (ORDER BY revenue DESC) AS revenue_quartile,
5
  NTILE(10) OVER (ORDER BY revenue DESC) AS revenue_decile
6
FROM customer_revenue;

1
SELECT *
2
FROM orders
3
WHERE customer_id IS NULL
4
   OR amount IS NULL
5
   OR status IS NULL
6
   OR created_at IS NULL;