DAY 5: DOCUMENTING MY SQL PROGRESS : CTEs Another day at the office, Chads. Today, I focused on doing one more Subquery and then CTEs. Here we go! --FRAME 1-- Query 1: This query retrieves products that have a price that is higher than the average price of products in their category. A challenge I encountered with this query was computing the Average price of products in each category. I initially used an aggregate function and a window function in the same SELECT statement. This is an illegal thing to do in SQL. This challenged the way I understood subqueries. A subquery is supposed to compute a value or multiple values that can be used by the outer query. Therefore, I sorted grouping with the 'avg_price_per_category' window function. This value is then compared to the price and category of the product in the outer query. Once the price in the subquery is greater than the average price of products in its own category, then that product is returned. --FRAME 2-- Query 2: Now onto CTEs. This particular CTE computes the average monthly revenue in the year 2024. This was pretty straight forward but I did not know where to add the final filter for monthly revenue that exceeds the average monthly revenue. I was also writing code that returned multiple rows of results. This cannot work since the ">" operand that I had in the 'WHERE' clause of the retrieving 'SELECT' statement would only product results for scalar inputs. So I had to use a sort of subquery in the retrieving 'SELECT' statement to compute the average monthly revenue. The revenue per month could then be compared to this average to determine which months exceeded the average monthly revenue. --FRAME 3-- Query 3: This query retrieves the top 10 customers by total spending. Very useful information for marketing and analytics purposes. This was easier than I thought. All I had to do was build the CTE. I got the customer name, total_spent by the customer, and their rank based on this spending. I had to use the JOIN to connect the orders, customers, and order_items tables. You can see the connecting columns that mandated the use of these JOINS. --FRAME 4-- Query 4: This was also very smooth. First CTE was to get the total revenue brought in by a customer as well as the number of orders they placed. JOINS were crucial for linking Foreign keys that made sense for data object connection. Second CTE was to know the number of reviews that each customer submitted. Very similar to the first CTE. When writing these CTEs, it is crucial to avoid the mistake of not using aliases correctly. Especially when JOINS are involved. By the Final query, I was just putting all I had retrieved from the first and second CTEs together. That's it fam!! We are making good progress here! Tomorrow we handle Window function. Please say hi if these explanations are not clear. or you think I am makin a mistake or even if there is a better way to do things. As always, corrections, suggestions and feedback is greatly appreciated. See you tomorrow, Lads!! #datafam #DataScience #dataengineering #SQL #CodingJourney #sqlbeginner