SQL Crash Course - Managing your own newsletters
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:
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 > (
ORDER BY i.newsletter_id, i.likes DESC;
Task3:
WITH MemberSpending AS (
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
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 (
i.published_date,
i.views
JOIN issues i ON n.newsletter_id = i.newsletter_id
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'
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 (
MIN(joined_date) AS first_date
),
MonthSeries AS (
first_date AS milestone_date,
0 AS month_number
FROM FirstSubscriptions
first_date + (month_number + 1) * INTERVAL '1 month',
month_number + 1
FROM MonthSeries
WHERE month_number < 3
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
i.newsletter_id,
n.name AS newsletter_name,
i.likes
JOIN newsletters n ON i.newsletter_id = n.newsletter_id
WHERE i.likes > 150;
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 (
GROUP BY m.country
RevenuePercentage AS (
revenue,
revenue / SUM(revenue) OVER () * 100 AS revenue_pct
FROM CountryRevenue
ROUND(revenue_pct, 2) AS revenue_pct
FROM RevenuePercentage
Task9:
plan,
COUNT(*) AS member_count
FROM members
GROUP BY country, plan
HAVING COUNT(*) > 5
ORDER BY country, plan;
Task10:
WITH NewsletterAverages AS (
AVG(views) AS avg_views
FROM issues
i.views,
na.avg_views
JOIN NewsletterAverages na ON i.newsletter_id = na.newsletter_id
WHERE i.views > na.avg_views
ORDER BY i.newsletter_id, i.views DESC;
Let me check them and come back to you soon!
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;
```
Let me check them and come back to you soon!