SQL Problem #2
SQL Crash Course - Managing your own newsletters
All the course material is stored in the SQL Crash Course repository.
Hi everyone! Cornellius Yudha Wijaya and
from 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
❓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(), thenRANK().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 JOINsubscriptions.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:
HAVINGvs.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
Here are my answers for your checking. Please let me know what kinds of rooms for improvements here.
Thanks
Task1:
```
SELECT
n.newsletter_id,
n.name,
SUM(s.price) AS revenue
FROM newsletters n
JOIN subscriptions s ON n.newsletter_id = s.newsletter_id
GROUP BY n.newsletter_id, n.name
HAVING SUM(s.price) > (
SELECT AVG(total_revenue)
FROM (
SELECT SUM(price) AS total_revenue
FROM subscriptions
GROUP BY newsletter_id
) sub
)
ORDER BY revenue DESC;
```
Task2:
```
SELECT
i.issue_id,
i.title,
i.likes,
(
SELECT AVG(likes)
FROM issues i2
WHERE i2.newsletter_id = i.newsletter_id
) AS avg_newsletter_likes
FROM issues i
WHERE i.likes > (
SELECT AVG(likes)
FROM issues i2
WHERE i2.newsletter_id = i.newsletter_id
)
ORDER BY i.newsletter_id, i.likes DESC;
```
Task3:
```
WITH MemberSpending AS (
SELECT
s.member_id,
m.name,
m.country,
SUM(s.price) AS total_spending
FROM subscriptions s
JOIN members m ON s.member_id = m.member_id
GROUP BY s.member_id, m.name, m.country
)
SELECT
member_id,
name,
country,
total_spending,
RANK() OVER (PARTITION BY country ORDER BY total_spending DESC) AS spending_rank
FROM MemberSpending
ORDER BY country, spending_rank;
```
Task4:
```
WITH IssueViews AS (
SELECT
n.newsletter_id,
i.published_date,
i.views
FROM newsletters n
JOIN issues i ON n.newsletter_id = i.newsletter_id
)
SELECT
newsletter_id,
published_date,
views,
SUM(views) OVER (
PARTITION BY newsletter_id
ORDER BY published_date
) AS cumulative_views
FROM IssueViews
ORDER BY newsletter_id, published_date;
```
Task5:
```
WITH RECURSIVE DateSeries AS (
SELECT '2025-01-01'::DATE AS date
UNION ALL
SELECT date + INTERVAL '1 day'
FROM DateSeries
WHERE date < '2025-12-31'
)
SELECT
d.date,
COUNT(s.subscription_id) AS new_subscriptions
FROM DateSeries d
LEFT JOIN subscriptions s ON d.date = s.joined_date
GROUP BY d.date
ORDER BY d.date;
```
Task6:
```
WITH RECURSIVE FirstSubscriptions AS (
SELECT
newsletter_id,
MIN(joined_date) AS first_date
FROM subscriptions
GROUP BY newsletter_id
),
MonthSeries AS (
SELECT
newsletter_id,
first_date AS milestone_date,
0 AS month_number
FROM FirstSubscriptions
UNION ALL
SELECT
newsletter_id,
first_date + (month_number + 1) * INTERVAL '1 month',
month_number + 1
FROM MonthSeries
WHERE month_number < 3
)
SELECT
m.newsletter_id,
m.milestone_date,
COUNT(s.subscription_id) AS cumulative_subscriptions
FROM MonthSeries m
LEFT JOIN subscriptions s
ON s.newsletter_id = m.newsletter_id
AND s.joined_date <= m.milestone_date
GROUP BY m.newsletter_id, m.milestone_date
ORDER BY m.newsletter_id, m.milestone_date;
```
Task7:
```
CREATE VIEW hot_issues AS
SELECT
i.issue_id,
i.newsletter_id,
n.name AS newsletter_name,
i.title,
i.likes
FROM issues i
JOIN newsletters n ON i.newsletter_id = n.newsletter_id
WHERE i.likes > 150;
SELECT
newsletter_id,
newsletter_name,
COUNT(issue_id) AS hot_issue_count
FROM hot_issues
GROUP BY newsletter_id, newsletter_name
ORDER BY hot_issue_count DESC;
```
Task8:
```
WITH CountryRevenue AS (
SELECT
m.country,
SUM(s.price) AS revenue
FROM subscriptions s
JOIN members m ON s.member_id = m.member_id
GROUP BY m.country
),
RevenuePercentage AS (
SELECT
country,
revenue,
revenue / SUM(revenue) OVER () * 100 AS revenue_pct
FROM CountryRevenue
)
SELECT
country,
revenue,
ROUND(revenue_pct, 2) AS revenue_pct
FROM RevenuePercentage
ORDER BY revenue DESC;
```
Task9:
```
SELECT
country,
plan,
COUNT(*) AS member_count
FROM members
GROUP BY country, plan
HAVING COUNT(*) > 5
ORDER BY country, plan;
```
Task10:
```
WITH NewsletterAverages AS (
SELECT
newsletter_id,
AVG(views) AS avg_views
FROM issues
GROUP BY newsletter_id
)
SELECT
i.issue_id,
i.title,
i.views,
na.avg_views
FROM issues i
JOIN NewsletterAverages na ON i.newsletter_id = na.newsletter_id
WHERE i.views > na.avg_views
ORDER BY i.newsletter_id, i.views DESC;
```