BBampaa

Lessons › SQL › Go further in SQL: Business questions

SQL lesson

Which weekday sells the most?

Which weekday sells the most? orders has dates, not weekdays, so the weekday has to be worked out from each date first, with TO_CHAR from Chapter 8. Then group by it and sort, biggest first.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Turn the date into a label, then group

SELECT TO_CHAR(order_date, 'Dy') AS weekday, SUM(revenue) AS revenue
FROM orders
GROUP BY TO_CHAR(order_date, 'Dy')
ORDER BY revenue DESC

Result:

weekdayrevenue
Fri185
Mon180
Sun150
Thu105
Wed94
Tue70

Step by step

  1. The top row answers the question.
    SELECT TO_CHAR(order_date, 'Dy') AS weekday, SUM(revenue) AS revenue FROM orders GROUP BY TO_CHAR(order_date, 'Dy') ORDER BY revenue DESC
  2. No join.
    SELECT TO_CHAR(order_date, 'Dy') AS weekday, SUM(revenue) AS revenue FROM orders GROUP BY TO_CHAR(order_date, 'Dy') ORDER BY revenue DESC
  3. No WHERE.
    SELECT TO_CHAR(order_date, 'Dy') AS weekday, SUM(revenue) AS revenue FROM orders GROUP BY TO_CHAR(order_date, 'Dy') ORDER BY revenue DESC
  4. TO_CHAR(order_date, 'Dy'), then GROUP BY it.
    SELECT TO_CHAR(order_date, 'Dy') AS weekday, SUM(revenue) AS revenue FROM orders GROUP BY TO_CHAR(order_date, 'Dy') ORDER BY revenue DESC

Practice

Try each one in your head first, then open the answer.

1. Which weekday sells the most? Show weekday (like Mon) and revenue, biggest first.
SELECT TO_CHAR(order_date, 'Dy') AS weekday, SUM(revenue) AS revenue FROM orders GROUP BY TO_CHAR(order_date, 'Dy') ORDER BY revenue DESC
2. How many orders came in on each weekday? Show weekday (like Mon) and orders, most first.
SELECT TO_CHAR(order_date, 'Dy') AS weekday, COUNT(*) AS orders FROM orders GROUP BY TO_CHAR(order_date, 'Dy') ORDER BY orders DESC
3. What's the revenue for each month, labelled like 2026-03? Show month and revenue, in month order.
SELECT TO_CHAR(order_date, 'YYYY-MM') AS month, SUM(revenue) AS revenue FROM orders GROUP BY TO_CHAR(order_date, 'YYYY-MM') ORDER BY month
4. What's the average order on each weekday? Show weekday (like Mon) and avg_order, biggest first.
SELECT TO_CHAR(order_date, 'Dy') AS weekday, AVG(revenue) AS avg_order FROM orders GROUP BY TO_CHAR(order_date, 'Dy') ORDER BY avg_order DESC
5. Which weekday had the most orders in March? Show weekday (like Mon) and orders, most first.
SELECT TO_CHAR(order_date, 'Dy') AS weekday, COUNT(*) AS orders FROM orders WHERE order_date >= DATE '2026-03-01' GROUP BY TO_CHAR(order_date, 'Dy') ORDER BY orders DESC

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed