Date Operations & Time-Based Analytics SQL Topic exerciseHardVerified answerSQLite + MySQL + SQL Server live · 2 guided

Rank the Busiest Transaction Hours

Aggregate orders by hour, rank hours by transaction count, and return the top two dense ranks.

  • Window functions
  • Subqueries
  • Aggregation
  • Date analysis
  • Type conversion

Exercise brief

Understand the request

Operations efficiency analyst Staffing planners need the busiest order-entry hours without losing tied ranks.

Staffing planners need the busiest order-entry hours without losing tied ranks. Aggregate orders by hour, rank hours by transaction count, and return the top two dense ranks.

Return

  • Return hour_of_day, transaction_count, total_amount, traffic_rank in this exact left-to-right order.

Constraints

  • Extract a numeric hour from order_timestamp.
  • Use DENSE_RANK so tied counts share a rank.
  • Order by count descending, amount descending, then hour.

Data you will use

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

orders

  • order_idINTEGER
  • order_totalDECIMAL
  • order_timestampDATETIME

Hints, when you need them

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

Hint 1

First aggregate count and amount at hour grain.

Hint 2

DENSE_RANK the hourly counts in descending order.

Hint 3

Filter the ranked CTE to traffic_rank <= 2 and add stable tie-breakers.

Verified SQL answer

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

Reveal solution and explanation
WITH hourly AS (SELECT CAST(strftime('%H', order_timestamp) AS INTEGER) AS hour_of_day, COUNT(*) AS transaction_count, SUM(order_total) AS total_amount FROM orders GROUP BY strftime('%H', order_timestamp)), ranked AS (SELECT hour_of_day, transaction_count, total_amount, DENSE_RANK() OVER (ORDER BY transaction_count DESC) AS traffic_rank FROM hourly) SELECT hour_of_day, transaction_count, total_amount, traffic_rank FROM ranked WHERE traffic_rank <= 2 ORDER BY transaction_count DESC, total_amount DESC, hour_of_day;

Why this works

Dense ranking preserves all hours tied at a requested traffic level, while a final deterministic order keeps equal-count rows stable.

Success check

The busiest hour and every hour tied at the second count level are returned with deterministic ordering.

Expected result

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

hour_of_daytransaction_counttotal_amounttraffic_rank
1049101
11310902
9310102
1438102

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.