Day 2- SQL FUNDAMENTALS-GROUP BY, WHERE, HAVING & CTEs

GROUP BY

GROUP BY puts rows with the same value into a group. Then aggregate functions calculate something for each group.

SELECT
   p.category,
   SUM(o.quantity * o.unit_price) AS total_revenue
 FROM orders o
 JOIN products p
 ON o.product_id = p.product_id
 GROUP BY p.category;

Example idea: Instead of 10,000 order rows, you might get one summary row per product category.

WHERE vs HAVING

⭐ Remember: WHERE filters rows. HAVING filters groups.

SELECT
   p.category,
   SUM(o.quantity * o.unit_price) AS total_revenue
 FROM orders o
 JOIN products p
 ON o.product_id = p.product_id
 WHERE o.status = 'completed'
 GROUP BY p.category
 HAVING SUM(o.quantity * o.unit_price) > 1000;

CTE — Common Table Expression

A CTE is a named temporary result that makes a complex query easier to read. Think of it as giving a name to an intermediate step.

WITH monthly_revenue AS (
 SELECT
   c.region,
   DATE_TRUNC(o.order_date, MONTH) AS month,
   SUM(o.quantity * o.unit_price) AS revenue
 FROM orders o
 JOIN customers c
 ON o.customer_id = c.customer_id
 GROUP BY c.region, DATE_TRUNC(o.order_date, MONTH)
 )
 SELECT *
 FROM monthly_revenue;

⭐ Remember: A CTE normally exists only for the statement in which it is defined. Its exact execution/materialization behavior depends on the database engine and query plan.

Get Stephy Thomas’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Practice — Average order value

Question: Find categories where the average order value is greater than $2,000.

SELECT
 p.category,
 AVG(o.quantity * o.unit_price) AS avg_order_value
 FROM orders o
 JOIN products p
 ON o.product_id = p.product_id
 GROUP BY p.category
 HAVING AVG(o.quantity * o.unit_price) > 2000;

Practice — Highest-revenue month per region

First calculate monthly revenue. Then compare each month with the maximum for its region.

WITH monthly_revenue AS (
 SELECT
   c.region,
   DATE_TRUNC(o.order_date, MONTH) AS month,
   SUM(o.quantity * o.unit_price) AS revenue
 FROM orders o
 JOIN customers c
 ON o.customer_id = c.customer_id
 GROUP BY c.region, DATE_TRUNC(o.order_date, MONTH)
 )
 SELECT
   region,
   month,
   revenue
 FROM monthly_revenue mr
 WHERE revenue = (
 SELECT MAX(mr2.revenue)
 FROM monthly_revenue mr2
 WHERE mr2.region = mr.region
 );

💡 Interview tip: This can also be solved elegantly with window functions such as RANK() or ROW_NUMBER().

⚡ 2-Minute Revision Sheet

Consistency is the key 🔑 Little by little, day by day, you grow 📈. Happy learning!