FAQ: Multiple Tables - Review


#1

This community-built FAQ covers the “Review” exercise from the lesson “Multiple Tables”.

Paths and Courses
This exercise can be found in the following Codecademy content:

Web Development
Data Science

Learn SQL

FAQs on the exercise Review

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!


#2

I’ve build up some tables and I’m happy with my result and I want to share it! I’m just a beginner of programming and thx for the courses I really learnt a lot!!

-- Table 1
WITH report_1 AS(
WITH report AS(
SELECT customer_id, subscription_id, COUNT(subscription_id) AS 'quantity'
FROM orders
GROUP BY 1,2)
SELECT report.customer_id, subscriptions.description, report.quantity
FROM subscriptions
JOIN report
ON subscriptions.subscription_id = report.subscription_id)
SELECT customers.customer_id AS 'ID', customers.customer_name AS 'Customer Name', report_1.description AS 'Magazine', report_1.quantity AS 'Quantity'
FROM customers
JOIN report_1
ON report_1.customer_id = customers.customer_id
ORDER BY 1;

-- Table 2 (Short Form)
WITH report_1 AS(
WITH report AS(
SELECT customer_id, subscription_id, COUNT(subscription_id) AS 'quantity'
FROM orders
GROUP BY 1,2)
SELECT report.customer_id, subscriptions.description, report.quantity
FROM subscriptions
JOIN report
ON subscriptions.subscription_id = report.subscription_id)
SELECT customers.customer_id AS 'ID', 
CASE
WHEN report_1.description LIKE '%Politics%' THEN 'P'
WHEN report_1.description LIKE '%Sports%' THEN 'S'
WHEN report_1.description LIKE '%Fashion%' THEN 'F'
ELSE NULL
END AS 'Magazine', 
report_1.quantity AS 'Quantity'
FROM customers
JOIN report_1
ON report_1.customer_id = customers.customer_id
ORDER BY 1;

-- Table 3 (for purchasing easy reference)
SELECT Magazine, COUNT(*)
FROM(
WITH report_1 AS(
WITH report AS(
SELECT customer_id, subscription_id, COUNT(subscription_id) AS 'quantity'
FROM orders
GROUP BY 1,2)
SELECT report.customer_id, subscriptions.description, report.quantity
FROM subscriptions
JOIN report
ON subscriptions.subscription_id = report.subscription_id)
SELECT customers.customer_id AS 'ID', 
CASE
WHEN report_1.description LIKE '%Politics%' THEN 'P'
WHEN report_1.description LIKE '%Sports%' THEN 'S'
WHEN report_1.description LIKE '%Fashion%' THEN 'F'
ELSE NULL
END AS 'Magazine', 
report_1.quantity AS 'Quantity'
FROM customers
JOIN report_1
ON report_1.customer_id = customers.customer_id
ORDER BY 1)
GROUP BY Magazine
ORDER BY 2;


#3

I tried all three of your queries but not one of them worked!?
I didn’t get any errors but I didn’t get any results either… very strange! Could you post a screen grab of your results?

Thanks for posting and tweaking my interest!

Dennis


#4

Reward your top 5 buyers!

WITH previous_query AS (
SELECT customer_id,
   COUNT(subscription_id) AS 'subscriptions'
FROM orders
GROUP BY customer_id
)
SELECT customers.customer_name,
   previous_query.subscriptions
FROM previous_query
JOIN customers
  ON previous_query.customer_id = customers.customer_id
  --sort the results!
  ORDER BY previous_query.subscriptions DESC
  LIMIT 5;

#5

Hi Dennis! Thx for your reply :]
I think it was the copy and paste issue since I was working this on evernote therefore some formatted text is pasted here. I’ve edited my post and I think the code can work now.
And nice idea for your table too! :wink: