SQL case studies: revenue share, donor segments and a 3NF schema
Three SQL studies on small course datasets. Each one starts with a business question. Every figure is query output that you can run again.
- Question
- Which genres drive revenue, and is the price in line with demand? Who are the major donors, and who stopped giving? How do we remove the anomalies in a flat table?
- Data
- The COMM 437 course schema: an album catalogue (25 albums), donors (14) and gifts (33), and a flat magazine subscription table.
- Method
- A CTE with SUM() OVER () for the revenue share in one pass. HAVING with a subquery for major donors, and an anti-join for lapsed donors. Functional dependencies to split the flat table into four 3NF tables.
- ResultPublished
- Pop gives 39.5% of catalogue revenue. 3 of 14 donors give 66% of gift value, and no campaign year reached its goal.
Coursework data. The tables are small (7 to 33 rows), so these studies show SQL and data-modelling skill, not business results.
1 · Pricing analysis with a CTE and a window function
A CTE adds up revenue by genre. SUM(...) OVER () then gives each genre its share of total revenue in the same query.
Finding: Pop gives 39.5% of revenue at an average price of $11.12. Only Jazz has a lower average price. That makes Pop the first candidate for a price test. A pricing claim needs a price-sensitivity model, so the recommendation stops at "test", not "raise".
2 · Schema redesign to third normal form
Problem: a flat subscription table caused update, insert and delete anomalies. When the last subscriber of a magazine left, the delete removed the magazine record too.
Work: I mapped the functional dependencies. Then I split the table into four 3NF tables with primary and foreign keys.
3 · Donor segments with subqueries and an anti-join
The major-donor query compares the total of each donor with two times the average gift, in a HAVING clause. The lapsed-donor query uses NOT IN. A LEFT JOIN … IS NULL version does the same work and is safer with NULL values.
Finding: 3 of 14 donors give 66% of gift value, and no campaign year reached its goal. The 12 donors who did not give in 1995 are the reactivation list.
Share of catalogue revenue by genre
| Item | Value (%) |
|---|---|
| Pop | 39.5 · 8 · $11.12 |
| Country | 12.3 · 3 · $11.99 |
| Alternative Rock | 12.3 · 3 · $11.99 |
| Classic Rock | 10.9 · 3 · $11.99 |
| Electronic | 7.6 · 2 · $13.49 |
| R&B | 7.1 · 2 · $12.49 |
| Jazz | 3.7 · 2 · $9.49 |
| Electro House | 3.5 · 1 · $14.99 |
| Dream Pop | 3.2 · 1 · $13.99 |
WITH genre_revenue AS (
SELECT
genre,
COUNT(aid) AS album_count,
SUM(sales_qty) AS total_units_sold,
ROUND(SUM(price * sales_qty), 2) AS total_revenue,
ROUND(AVG(price), 2) AS avg_price
FROM album
GROUP BY genre
HAVING SUM(sales_qty) > 100
)
SELECT
genre,
album_count,
total_revenue,
avg_price,
ROUND(total_revenue * 100.0 / SUM(total_revenue) OVER (), 1) AS revenue_share_pct
FROM genre_revenue
ORDER BY total_revenue DESC, genre;SELECT
d.donor_no,
d.first_name,
d.last_name,
ROUND(SUM(g.amount), 2) AS total_giving
FROM donor d
JOIN gift g ON d.donor_no = g.donor_no
GROUP BY d.donor_no, d.first_name, d.last_name
HAVING SUM(g.amount) >= 2 * (SELECT AVG(amount) FROM gift)
ORDER BY total_giving DESC, d.donor_no;More work
- Stay for the exhale
I turned a yoga teacher's classes into a membership product. I did the strategy, the design, the build and the operations.
- A bank reconciliation that explains each difference
A working sample of the reconciliation method from my years as Operations & Finance Lead. Excel formulas match 119 bank lines to 121 ledger entries, explain each difference and reconcile to 0.00.
- What 218,812 Airbnb reviews talk about
A Python check and keyword scan of a large review dataset. It turns free text into a service-quality snapshot, with aggregate counts only.