SQL Mini Project (Build a KPI Dashboard Query Set)

Goal

Create a mini “query pack” for a dashboard.

Assume tables:

• users(user_id, created_at, country, plan)
• orders(order_id, user_id, order_ts, amount)
• 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;

2) Daily revenue

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;

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;

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 BY, joins, 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

Check your understanding

🧪 Try It Yourself

Exercise 1 – DAU query

Press Run to see what your code prints.

Exercise 2 – Top countries by revenue

Press Run to see what your code prints.

Exercise 3 – ARPU via a CTE

Press Run to see what your code prints.

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 pandas.