Databases Project
SQL Analytics Dashboard
An analytics dashboard project is where SQL stops being CRUD and starts being analysis: cohorts, running totals, period-over-period comparisons and rankings, all expressed in window functions. The skill it builds — turning a business question into a query — is the core of every data analyst role.
The brief
Starting from a transactional dataset, answer four business questions with SQL — retention, growth, top performers and a trend — and present each as one chart. Make the queries fast enough to refresh live.
What it demonstrates
That you can write analytical SQL, not just SELECT statements, and that you know why a query is slow.
What "done" looks like
Build all of these and the project is finished. Anything past that is in the stretch goals.
- A transactional dataset large enough to be interesting
- A cohort retention query
- A period-over-period growth comparison
- A ranking query using window functions
- A trend chart over time
- Every query fast enough to refresh on load
How to build it
- 1
Get transactional data
Orders, trips, events — something not pre-aggregated. The aggregation is what you are demonstrating.
- 2
Write the four questions down
Decide the business questions before writing SQL. A dashboard built from available columns answers nothing.
- 3
Build the cohort query
Group users by join period, measure activity in each later period. This is the query that separates growth from retention.
- 4
Add running totals and rankings
SUM OVER, ROW_NUMBER, LAG. Once these click, most analytical questions become one query.
- 5
Read the query plans
EXPLAIN ANALYZE each one. Find the sequential scan, add the index, measure the difference.
- 6
Chart one question each
A dashboard where every tile answers something. A wall of metrics gets ignored within a fortnight.
- 7
Decide about precomputation
Only materialise once you have measured. Reaching for a pipeline first is how small projects grow one they never needed.
Once it works
Only after the definition of done is met. Half-finished ambition reads worse than a small finished thing.
- Add a star schema and compare query speed against the raw tables
- Schedule a refresh and detect stale data
- Add anomaly detection on the trend and alert on it
Frequently Asked Questions
Where do I get realistic data?
Public datasets with a transactional shape — retail orders, transit trips, public spending — or generate your own with a seeding script. Avoid pre-aggregated data, since the aggregation is the part you are meant to be demonstrating.
What is a cohort analysis?
Grouping users by when they joined, then measuring behaviour over time within each group. It separates "we grew" from "we retained", which are different claims that a total-users chart cannot distinguish — and that is why it is the standard first question.
Should I precompute or query live?
Query live first, then measure. If a dashboard query takes seconds, add indexes; if it still does, materialise the summary and refresh on a schedule. Reaching for precomputation before measuring is how simple projects acquire pipelines they never needed.