Goal

Create a mini “query pack” for a dashboard.

Assume tables:

• users(user_id, created_at, country, plan)users(user_id, created_at, country, plan)
• orders(order_id, user_id, order_ts, amount)orders(order_id, user_id, order_ts, amount)
• events(user_id, event_ts, event_name)events(user_id, event_ts, event_name)

1) Daily active users (DAU)

SELECT
  DATE(event_ts) AS day,
  COUNT(DISTINCT user_id) AS dau
FROM events
GROUP BY DATE(event_ts)
ORDER BY day;

SELECT
  DATE(event_ts) AS day,
  COUNT(DISTINCT user_id) AS dau
FROM events
GROUP BY DATE(event_ts)
ORDER BY day;

2) Daily revenue

SELECT
  DATE(order_ts) AS day,
  SUM(amount) AS revenue
FROM orders
GROUP BY DATE(order_ts)
ORDER BY day;

SELECT
  DATE(order_ts) AS day,
  SUM(amount) AS revenue
FROM orders
GROUP BY DATE(order_ts)
ORDER BY day;

3) Top countries by revenue

SELECT
  u.country,
  SUM(o.amount) AS revenue
FROM orders o
JOIN users u ON u.user_id = o.user_id
GROUP BY u.country
ORDER BY revenue DESC
LIMIT 10;

SELECT
  u.country,
  SUM(o.amount) AS revenue
FROM orders o
JOIN users u ON u.user_id = o.user_id
GROUP BY u.country
ORDER BY revenue DESC
LIMIT 10;

4) ARPU (avg revenue per user)

WITH revenue_by_user AS (
  SELECT user_id, SUM(amount) AS revenue
  FROM orders
  GROUP BY user_id
)
SELECT
  AVG(revenue) AS arpu
FROM revenue_by_user;

WITH revenue_by_user AS (
  SELECT user_id, SUM(amount) AS revenue
  FROM orders
  GROUP BY user_id
)
SELECT
  AVG(revenue) AS arpu
FROM revenue_by_user;

Deliverable

• 5–10 queries you can reuse
• Notes about assumptions (time zone, filters, returns)

How the query pack fits together

A “KPI dashboard” is really just several independent queries against the same handful of tables, each answering one question. Nothing here is new — you’re combining GROUP BYGROUP BY, joins, DATE()DATE(), and CTEs from earlier pages into small, named building blocks.

  flowchart LR
  A["events"] --> B["DAU query"]
  C["orders"] --> D["Daily revenue query"]
  C --> E["Top countries query"]
  F["users"] --> E
  C --> G["ARPU query (via CTE)"]
  B --> H["Dashboard"]
  D --> H
  E --> H
  G --> H

🧪 Try It Yourself

Exercise 1 – DAU query

Exercise 2 – Top countries by revenue

Exercise 3 – ARPU via a CTE

Next

This wraps up the SQL for Data Analytics phase. Bring these query patterns with you into the project phases, where you’ll pull data from files instead of a database — the filtering, grouping, and joining logic transfers directly to pandaspandas.