Skip to content
Python Central Hub

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)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)

DAU
SELECT
  DATE(event_ts) AS day,
  COUNT(DISTINCT user_id) AS dau
FROM events
GROUP BY DATE(event_ts)
ORDER BY day;
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;
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;
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;
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)

If this helped you, consider buying me a coffee ☕

Buy me a coffee

Was this page helpful?

Let us know how we did