SQL Problem #2

SQL Crash Course - Managing your own newsletters

Cornellius Yudha Wijaya

May 29, 2025

Cross-post from Non-Brand Data

Today’s second problem will help refresh our understanding of topics 4 and 6! Let's dive in! 🔥 -

Josep Ferrer

All the course material is stored in the SQL Crash Course repository.

Hi everyone! Cornellius Yudha Wijaya and

Josep Ferrer

from

DataBites

here👋🏻

As promised, today we are publishing the next two issues of our SQL Crash Course – From Zero to Hero! 🚀

I am sure you are here to continue our SQL Crash Course Journey!📚

If this is your first time or you’ve forgotten what we’ll cover, we will examine seven key SQL topics, each divided into multiple posts across Non-Brand Data and DataBites.

We have learned a great deal about SQL; now it is time for you to apply it to the problems that exist in the real world.

In the next two weeks, Josep and I will send you a simple problem that will test your knowledge using everything we have learn in our previous SQL Crash Courses.

In today’s topic, we will test the following topics:

4️⃣ Advanced SQL

#11. Subqueries → link
#12. Common Table Expressions (CTEs) → link
#13. Recursion → link
#14. Views → link

6️⃣ Crafting Good SQL queries

#18. Modular Code →link
#19. SQL Execution Order → link
#20. Query Optimization → link

❓Context

You manage two growing newsletters — DataBites.tech and Non-Brand Data.
Each newsletter has issues, views, and likes. Users can subscribe for free or with a paid monthly plan.

In this problem, you’ll use SQL to answer questions about newsletter performance, member engagement, and revenue insights.

🧱 Database Schema

Sample data has already been loaded and includes:

2 newsletters (DataBites & Non-Brand Data)
30 issues
50 members
50 subscriptions (mix of free and paid)

You can check the schema in the following SQL playground.

🧩 Tasks

Use SQL to answer the following questions:

1. List newsletters with above-average monthly revenue

Show newsletter_id, name, and revenue for newsletters exceeding the average revenue across all newsletters.

Hint: Subquery to calculate average revenue.
Keywords: SELECT, GROUP BY, HAVING, subquery.

2. Find issues with more likes than their newsletter's average

Display issue_id, title, likes, and the newsletter's average likes.

Hint: Correlated subquery in SELECT.
Keywords: Correlated subquery, AVG().

3. Rank members by subscription spending per country

Use a CTE to calculate total spending per member. List member_id, name, country, total_spending, and their spending rank within their country.

Hint: CTE with SUM(), then RANK().
Keywords: WITH, SUM(), RANK(), PARTITION BY.

4. Calculate cumulative views per newsletter over time

Using a CTE, show newsletter_id, published_date, views, and cumulative views chronologically.

Hint: CTE with ORDER BY published_date.
Keywords: WITH, SUM() OVER(), ORDER BY.

5. Generate a date series for 2025 and count new subscriptions

Use a recursive CTE to generate all dates in 2025. For each date, show the number of new subscriptions (joined_date).

Hint: Recursive CTE to generate dates, then LEFT JOIN subscriptions.
Keywords: RECURSIVE, GENERATE_SERIES, LEFT JOIN.

6. Track subscription growth milestones per newsletter

Using recursion, show the first 3 months after each newsletter's first subscription date and cumulative subscriptions at each interval.

Hint: Anchor: first subscription date per newsletter. Recursive: add 1 month intervals.
Keywords: RECURSIVE, UNION ALL, INTERVAL.

7. Create a view for high-engagement issues

Define a view hot_issues showing issues with >150 likes. Then query it to count issues per newsletter.

Hint: CREATE VIEW + GROUP BY.
Keywords: CREATE VIEW, WHERE, GROUP BY.

8. Modular revenue analysis by country

Break into CTEs:

CTE1: Total revenue per country.

CTE2: Country's percentage of global revenue.

Final output: country, revenue, revenue_pct.

Hint: Two CTEs chained together.
Keywords: WITH, multiple CTEs.

9. Debug query with incorrect aggregation

Given this flawed query:

SELECT country, COUNT(*) AS member_count, plan
FROM members
WHERE member_count > 5  -- Error!
GROUP BY country, plan;

Fix it by understanding execution order.

Hint: HAVING vs. WHERE.
Keywords: Execution order, HAVING.

10. Optimize a slow-running issue analysis

The following subquery for newsletter average views is slow.

SELECT 
    i1.issue_id, 
    i1.title, 
    i1.views,
    (SELECT AVG(views) FROM issues i2 
     WHERE i2.newsletter_id = i1.newsletter_id) AS avg_views
FROM issues i1
WHERE i1.views > (
    SELECT AVG(views) FROM issues i3 
    WHERE i3.newsletter_id = i1.newsletter_id
);

Rewrite using a CTE to precompute averages, which will optimize the analysis.

Hint: Replace correlated subquery with CTE + JOIN.
Keywords: Optimization, CTE vs. subquery.

‼️ Next week, you’ll get the answers to all the tasks!

📥 How to submit?

Open the shared SQL playground from earlier and make a copy to work on your answers.
Once you're done, leave a comment below to confirm your participation and share the link to your playground.
Feel free to share your thoughts or questions as well!

Let’s make this week count 🔥

How to Get Started 🚀

Over the coming weeks, we’ll guide you through:

✅ SQL Fundamentals
✅ Intermediate SQL
✅ Advanced SQL
✅ Database Operations
✅ Writing Efficient Queries

Once you grasp the basics, practice is key! Start working on real-world projects and solving hands-on data problems.

What’s Next? ➡️

This is the first of many posts about the upcoming SQL Courses. It will only explain what SQL is in its crude form.

To get the full experience and fully immersed in the learning:

👉 Subscribe to Databites.tech (By Josep)

👉 Subscribe to Non-Brand Data (By Cornellius)

👉 Check out the SQL Crash Course GitHub repo

👉 Share with your friend and whoever needs it!

🗓️ Every Thursday, you will have two new issues in your inbox!

Let’s dive in and make SQL less scary, more fun, and way more useful! 🚀

Josep & Cornellius