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)

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

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

Top countries

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)

ARPU

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.

diagram KPI query pack mermaid

  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

Each dashboard number is its own small query against the same shared tables; a dashboard just runs all of them and displays the results side by side.

Analyst’s tip

Write each KPI query so it can run standalone with no hidden state — no relying on a temp table left over from a previous query, no assuming today’s date. A query pack you can paste into a fresh connection and run top-to-bottom is one you (or a teammate) can actually trust six months from now.

🧪 Try It Yourself

Exercise 1 – DAU query

pch.exerciseTag pch.exerciseTitle

Exercise 2 – Top countries by revenue

pch.exerciseTag pch.exerciseTitle

Exercise 3 – ARPU via a CTE

pch.exerciseTag pch.exerciseTitle

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.