Skip to content
Quantoros

Return the single day of the week (e.g. Monday) with the most transactions, and its count. Break ties alphabetically…

SQLLevel: IntermediateBonus drills (mixed difficulty)

Return the single day of the week (e.g. Monday) with the most transactions, and its count. Break ties alphabetically by day name.

Table definitions (from the source exercise):

CREATE TABLE transactions(transaction_id INTEGER PRIMARY KEY, account_id INTEGER, transaction_date TEXT, amount REAL, merchant_category TEXT);
Hints
  • Write an interleaving of two concurrent operations.
  • Which observations does the isolation level permit, and what happens on a mid-operation failure?

Answer

Approach: DAYNAME(transaction_date), GROUP BY it, ORDER BY count DESC then day name, LIMIT 1.

SELECT DAYNAME(transaction_date) AS day_name, COUNT(*) AS num_txns
FROM transactions
GROUP BY day_name
ORDER BY num_txns DESC, day_name
LIMIT 1;

The source uses SQLite with a MySQL compatibility layer. Date and string functions are dialect dependent; the query is preserved as published.

Source and licence: SQL Practice Lab — Ebrahim Sharifi · MIT

Related questions