Write a query to find all orders placed on weekends versus weekdays and compare average order value.

Answer

Classify each order's day of week as weekend or weekday, then compare average order value between the two groups.

Solution

SELECT
  CASE WHEN DAYOFWEEK(order_date) IN (1, 7) THEN 'Weekend' ELSE 'Weekday' END AS day_type,
  COUNT(*) AS order_count,
  ROUND(AVG(order_value), 2) AS avg_order_value
FROM Orders
GROUP BY CASE WHEN DAYOFWEEK(order_date) IN (1, 7) THEN 'Weekend' ELSE 'Weekday' END;

Step by Step

1. DAYOFWEEK(order_date) returns a numeric day of the week (convention varies by engine; commonly 1 = Sunday through 7 = Saturday).
2. The CASE expression labels Sunday and Saturday (values 1 and 7) as 'Weekend', everything else as 'Weekday'.
3. Grouping by that same CASE expression collapses all orders into exactly two buckets, and AVG(order_value) gives each bucket's average.

Cleaner Version Using a CTE to Avoid Repeating the CASE Expression

WITH classified AS (
  SELECT order_value,
         CASE WHEN DAYOFWEEK(order_date) IN (1, 7) THEN 'Weekend' ELSE 'Weekday' END AS day_type
  FROM Orders
)
SELECT day_type, COUNT(*) AS order_count, ROUND(AVG(order_value), 2) AS avg_order_value
FROM classified
GROUP BY day_type;

Computing the classification once in a CTE and grouping by the resulting column name avoids writing the same CASE expression twice - easier to maintain if the weekend definition ever changes.

Key Exam Traps

• DAYOFWEEK numbering is engine-specific: MySQL uses 1=Sunday...7=Saturday, while PostgreSQL's EXTRACT(DOW FROM date) uses 0=Sunday...6=Saturday - always verify the exact convention for the target engine rather than assuming.
• Locale/regional weekend definitions: Some countries treat Friday-Saturday as the weekend rather than Saturday-Sunday - a business-context detail worth confirming rather than assuming a Western default.
• GROUP BY on a CASE expression must match exactly: Some SQL engines require repeating the identical expression in GROUP BY, while others allow referencing a SELECT-clause alias - know which is required for the engine in question.