MULTIPLE TABLES. Can I use WITH twice?

Hello,

I am doing freestyle training, and I got stuck. Can you help me, pls?

I am trying to answer the following question: How much does a user paid on average (price_per_month)?

The table I am lookinf to get is something like this:

… but instead of the customer_id I want to see the customer_name

I tried to use WITH twice, but not sure if this is correct. I am leaving the code I have developed here to see if you can also give me feedback.

WITH previous_query AS (
SELECT subscription_id, 
  AVG(price_per_month) AS 'average' 
FROM subscriptions 
GROUP BY subscription_id  
) 
AND previous_result AS (
SELECT orders.customer_id,previous_query.average 
FROM previous_query
JOIN orders
  ON previous_query.subscription_id = orders.customer_id
)
SELECT customers.customer_name, previous_query.average 
FROM previous_result
JOIN customers
  ON previous_result.customer_id = customers.customer_name;

Thks,

Hi, welcome to the Forums!

But don’t you just need to join the temporary table with the Customers table?

1 Like

I did it!

SELECT  customers.customer_id, customers.customer_name, ROUND(AVG(subscriptions.price_per_month), 2) AS 'average'
FROM subscriptions
LEFT JOIN 
(customers LEFT JOIN orders ON customers.customer_id = orders.customer_id)
ON subscriptions.subscription_id = orders.subscription_id
GROUP BY 1
ORDER BY 3 DESC;