9/10: too simple of an execution to learn from


I was able to get the same table output using previously taught methods (them being just joining orders and customers). It made me initially think that WITH was useless. I then tried, on my own, using WITH as a way to join 3 tables together and I realized the true potential of the WITH operation. I just think that doing something more complicated would help future people learning SQL see what WITH can really be used for.

For anyone interested the code I made was to combine all three tables to display each person’s name and how much they owed for those transactions WITH first_join AS (SELECT customers.customer_name, COUNT(orders.subscription_id) AS 'subscriptions', orders.subscription_id AS 'sub_id', orders.customer_id AS 'customer_id' FROM orders JOIN customers ON orders.customer_id = customers.customer_id GROUP BY orders.customer_id) SELECT first_join.customer_name AS 'Name', SUM(subscriptions.price_per_month) AS 'Total Payment' FROM first_join JOIN subscriptions ON first_join.sub_id = subscriptions.subscription_id GROUP BY first_join.customer_id;


This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.