Date Operations & Time-Based Analytics SQL Topic exerciseMediumVerified answerSQLite + PostgreSQL live · 3 guided

Weekend vs Weekday Revenue Split

Return order count, revenue, and average order value for Weekend and Weekday groups.

  • Aggregation
  • CASE expressions
  • Date analysis
  • Numeric functions
  • Type conversion

Exercise brief

Understand the request

Commercial analytics manager Channel planning compares weekend purchasing with weekday purchasing.

Channel planning compares weekend purchasing with weekday purchasing. Return order count, revenue, and average order value for Weekend and Weekday groups.

Return

  • Return day_type, order_count, total_revenue, avg_order_value in this exact left-to-right order.

Constraints

  • Classify both Saturday and Sunday as Weekend.
  • Aggregate after deriving the day type.
  • Order by total revenue descending.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

orders

  • order_idINTEGER
  • order_dateDATE
  • order_totalDECIMAL

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Weekend = weekday number 0 (Sunday) or 6 (Saturday) in SQLite/Postgres.

Hint 2

Use a CASE to label each order, then GROUP BY that label.

Hint 3

avg_order_value = AVG(order_total) within each day_type.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT CASE WHEN CAST(strftime('%w', order_date) AS INTEGER) IN (0, 6) THEN 'Weekend' ELSE 'Weekday' END AS day_type, COUNT(*) AS order_count, ROUND(SUM(order_total), 2) AS total_revenue, ROUND(AVG(order_total), 2) AS avg_order_value FROM orders GROUP BY day_type ORDER BY total_revenue DESC;

Why this works

Weekend-vs-weekday splits inform staffing, promotions, and capacity. The logic is a binary CASE over the weekday number — but remember each engine numbers weekdays differently (SQLite/PG 0=Sun, MySQL 1=Sun), so the IN-list must match the engine.

Success check

Every order belongs to exactly one of the two groups and totals reconcile to the source.

Expected result

Use this output to verify values, aliases, ordering, and row count.

day_typeorder_counttotal_revenueavg_order_value
Weekday123660305
Weekend82330291.25

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.