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.

Role
Analyst (coursework)
Period
2024 – 2025
Stack
SQL, CTEs, Window functions, 3NF
Evidence
Published
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.

39.5%Pop share of revenue
66%Gift value from the top 3 donors
12Lapsed donors to contact
4Tables after 3NF

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

Share of catalogue revenue by genre
ItemValue (%)
Pop39.5 · 8 · $11.12
Country12.3 · 3 · $11.99
Alternative Rock12.3 · 3 · $11.99
Classic Rock10.9 · 3 · $11.99
Electronic7.6 · 2 · $13.49
R&B7.1 · 2 · $12.49
Jazz3.7 · 2 · $9.49
Electro House3.5 · 1 · $14.99
Dream Pop3.2 · 1 · $13.99
01_music_genre_revenue.sql
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;
03_donor_major_donor_identification.sql
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;