The question
How can a normalized rental database be turned into decision-ready revenue summaries while preserving the difference between total payments and payments that successfully connect through the analytical join path?
Data and reconciliation
| Reconciliation item | Amount |
|---|---|
| All payments | $67,416.51 |
| Revenue linked through rental records | $67,406.56 |
| Five unmatched payments | $9.95 |
| Actors represented | 200 |
Interpretation: the actor result is revenue associated with films in which an actor appears. It is not a causal measure of that actor’s contribution to demand.
What I built
- MySQL queries using joins, grouped aggregation, common table expressions, and window functions.
- A Dockerized database setup using the canonical Sakila fixture.
- A Python and SQLAlchemy notebook path for running queries and reviewing outputs.
- Saved result tables and a README that explains setup, assumptions, checks, and limitations.
Quality controls
Five checks verify core table counts, actor coverage, payment totals, linked revenue, and the unmatched-payment exception. Query-plan review and spot checks are documented alongside the main workflow.
One limitation
Sakila is a fictional teaching database, so this remains a demonstration of SQL and reconciliation technique. A business version would add customer cohorts, rental frequency, inventory availability, and margin.