T20 League Fixtures Played on a Weekend in 2024
Weekend matches carry a higher ticket price, so the finance team wants the list of them for the 2024 calendar year.
- Difficulty: Easy
- Topics: Dates
- Dialect: MySQL
- Problem: #454
Problem statement
Weekend matches carry a higher ticket price, so the finance team wants the list of them for the 2024 calendar year. The league's season spills over from December into January, so the table holds fixtures from more than one year.
Return every fixture played on a Saturday or Sunday with a match_date in 2024, with the columns match_id, match_date and day_name (the full English name of the day, such as Saturday). Order the rows by match_date, then by match_id.
Tables
Table: T20Fixture
| Column | Type |
|---|---|
| match_id | int |
| match_date | date |
| home_team | varchar |
| away_team | varchar |
Primary key: match_id.
One row per scheduled league fixture.
Examples
Example 1
T20Fixture
| match_id | match_date | home_team | away_team |
|---|---|---|---|
| 1 | 2023-12-30 | Mumbai Monarchs | Chennai Cheetahs |
| 2 | 2024-01-05 | Delhi Defenders | Jaipur Jaguars |
| 3 | 2024-01-06 | Kolkata Krakens | Punjab Pioneers |
| 4 | 2024-01-07 | Chennai Cheetahs | Delhi Defenders |
| 5 | 2024-01-07 | Lucknow Lancers | Mumbai Monarchs |
| 6 | 2024-01-10 | Jaipur Jaguars | Kolkata Krakens |
| 7 | 2024-12-28 | Punjab Pioneers | Lucknow Lancers |
| 8 | 2025-01-04 | Mumbai Monarchs | Delhi Defenders |
Output
| match_id | match_date | day_name |
|---|---|---|
| 3 | 2024-01-06 | Saturday |
| 4 | 2024-01-07 | Sunday |
| 5 | 2024-01-07 | Sunday |
| 7 | 2024-12-28 | Saturday |
How to solve T20 League Fixtures Played on a Weekend in 2024
Two date conditions and one date function. The year filter can be YEAR(match_date) = 2024 or a range BETWEEN '2024-01-01' AND '2024-12-31'; the range form can use an index on match_date, because it does not wrap the column in a function.
For the weekend, MySQL offers two numberings and it is easy to mix them up. DAYOFWEEK follows the ODBC convention, Sunday = 1 through Saturday = 7, so the weekend is IN (1, 7). WEEKDAY starts at Monday = 0, so Saturday and Sunday are 5 and 6 and the test is >= 5. Comparing DAYNAME(...) with the names also works. All three agree.
The example includes fixtures on 30 December 2023 and 4 January 2025, both on weekends, to show why the year filter is needed. The output is ordered by date, with match_id breaking the tie between two matches on one day. One scan of the table.
Reference solution (MySQL)
SELECT match_id, match_date, DAYNAME(match_date) AS day_name
FROM T20Fixture
WHERE YEAR(match_date) = 2024
AND DAYOFWEEK(match_date) IN (1, 7)
ORDER BY match_date, match_idAnother way
SELECT match_id, match_date, DAYNAME(match_date) AS day_name FROM T20Fixture WHERE match_date BETWEEN '2024-01-01' AND '2024-12-31' AND WEEKDAY(match_date) >= 5 ORDER BY match_date, match_idAnother way
SELECT match_id, match_date, DAYNAME(match_date) AS day_name FROM T20Fixture WHERE DATE_FORMAT(match_date, '%Y') = '2024' AND DAYNAME(match_date) IN ('Saturday', 'Sunday') ORDER BY match_date, match_id← Season Attendance at Each Football Stadium · Batters Who Were Out for a Duck This Season →