Query 1 shows that data pulled from distant parts of a schema can be compared in a single analysis. But there is no expectation for what the results of that analysis should be. Query 2 goes a step further. There is an expectation for what the results of this analysis should be (all customers have paid exactly what their rental history dictates they owe). Any variance from that expectation then becomes the analysis. The most reasonable expected variance is that certain customers are net underpaid. However, this database reveals an even more interesting variance: no individual customer has paid exactly what they owe, and only 4% are net underpaid. But a whopping 96% are net overpaid. In fact, entire countries are net overpaid. 105 out of 108 countries are net overpaid. This particular gap could become useful for opening an investigation into fraud, billing processes, or a database bug.
In general, comparing an actual outcome to an expected baseline — a variance analysis — can provide useful information anywhere an expected outcome exists. For example, in an airline database every leg of a purchased multi-leg ticket is expected to show a matching boarding scan. Occasional no-shows are normal noise. But if unused legs cluster disproportionately at the end of itineraries rather than scattering randomly across all legs, it could be an indication that travelers are buying multi-leg fares to reach an interim destination because it’s cheaper than a direct ticket.
Annotated Schema
Tables used in this query are highlighted. Others are shown for context.
Reasoning
Comparing paid versus owed per rental, then rolling it up to paid versus owed per country means seven joins in a single chain to place each rental’s paid and owed amounts inside a specific country. This step is fundamentally the same as Query 1 — joining tables and columns from disparate parts of the schema.
The LEFT JOIN in the first CTE exists because of a specific discovery in the exploratory queries: some rentals had no payment row at all. An inner join would have silently dropped those unpaid rentals from the analysis entirely, understating how much was actually owed. The CASE WHEN alongside it converts those missing payments to $0.00, so they still count toward the total owed even though nothing was collected.
Once every rental carries a country, table2 sums amount_paid and amount_due grouped by country, then divides the two sums and subtracts 1 to produce a rate — the “National Average Overpayment Rate” — that puts every country on the same scale regardless of size.
table3 isolates the top decile of that rate. WHERE national_average_overpayment_rate > 0 first drops any country that underpaid on average, so the ranking only considers overpaying countries. NTILE(10), ordered by that rate with no partition, then splits the remaining countries into ten equal-sized groups from highest rate to lowest; the final query keeps only decile 1 — the top 10% of overpaying countries specifically.
/* table1 creates a table of every rental_id, the country where the customer lives, the amount due, and the amount paid for the rental. Running a couple initial queries determines that payment amounts do not necessarily correlate with the rental_rate for a given film. In some cases, payment was made but it was more or less than the rental_rate. In some cases, a payment was made but it was $0.00. In one case, multiple payments were made for a single rental (rental_id 4591 appears in 5 different records with unique payments_id's). Since there are some cases where no payment was made (a rental_id does not appear in any record in the payment database), the payment database needs to be LEFT JOINed to capture those records from the rental database that do not have a rental_id in the payment database. The CASE WHEN statement is used to give the resulting records with a NULL value for p.amount a value so that they can be used to calculate balances in table2 */
WITH table1 AS (
SELECT r.rental_id,
c3.country,
f.rental_rate AS amount_due,
CASE WHEN p.amount IS NULL THEN '0.00'
ELSE p.amount END AS amount_paid
FROM customer c
JOIN rental r
ON c.customer_id = r.customer_id
LEFT JOIN payment p
ON p.rental_id = r.rental_id
JOIN inventory i
ON i.inventory_id = r.inventory_id
JOIN film f
ON f.film_id = i.film_id
JOIN address a
ON c.address_id = a.address_id
JOIN city c2
ON a.city_id = c2.city_id
JOIN country c3
ON c2.country_id = c3.country_id
ORDER BY 2
),
/* table2 sums the amount paid, amount due, and divides the two to determine the National Average Overpayment Rate, grouping by country, ordering from highest to lowest by National Average Overpayment Rate. */
table2 AS (
SELECT country,
SUM (amount_paid) AS total_amount_paid,
SUM (amount_due) AS total_amount_due,
(SUM (amount_paid) / SUM (amount_due)) - 1 AS national_average_overpayment_rate
FROM table1
GROUP BY 1
ORDER BY 4 DESC
),
/* table3 uses a Window Function to determine the top 10% of countries by Country-Wide Overpayment Rate, including only countries that have overpaid (some countries have underpaid). */
table3 AS (
SELECT country,
national_average_overpayment_rate,
NTILE (10) OVER (ORDER BY national_average_overpayment_rate DESC) AS national_average_overpayment_rate_decile
FROM table2
WHERE national_average_overpayment_rate > 0
)
/* The final output shows the country and overpayment rate of only countries in the top 10%. */
SELECT country,
national_average_overpayment_rate
FROM table3
WHERE national_average_overpayment_rate_decile = 1;
Appendix
*Displayed as ‘Runion’ to match the database’s stored value (missing its accent); the actual country is Réunion.
Key Finding
Among countries with a positive National Average Overpayment Rate, 11 fall in the top 10%. Iraq leads at 62.56%, followed closely by French Guiana (58.30%) and Greenland (58.23%). The remaining eight range from Afghanistan (54.77%) down to Réunion (43.38%).