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;