T20 League Fixtures Played on a Weekend in 2024

Easy
454.

T20 League Fixtures Played on a Weekend in 2024

EasyDates

MySQL

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

ColumnType
match_idint
match_datedate
home_teamvarchar
away_teamvarchar

Primary key: match_id.

One row per scheduled league fixture.

Example

The rows must come back in the order shown.

T20Fixture

match_idmatch_datehome_teamaway_team
12023-12-30Mumbai MonarchsChennai Cheetahs
22024-01-05Delhi DefendersJaipur Jaguars
32024-01-06Kolkata KrakensPunjab Pioneers
42024-01-07Chennai CheetahsDelhi Defenders
52024-01-07Lucknow LancersMumbai Monarchs
62024-01-10Jaipur JaguarsKolkata Krakens
72024-12-28Punjab PioneersLucknow Lancers
82025-01-04Mumbai MonarchsDelhi Defenders

Output

match_idmatch_dateday_name
32024-01-06Saturday
42024-01-07Sunday
52024-01-07Sunday
72024-12-28Saturday
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
match_idint
match_datedate
home_teamvarchar
away_teamvarchar

Primary key: match_id.

One row per scheduled league fixture.

Examples

Example 1

T20Fixture

match_idmatch_datehome_teamaway_team
12023-12-30Mumbai MonarchsChennai Cheetahs
22024-01-05Delhi DefendersJaipur Jaguars
32024-01-06Kolkata KrakensPunjab Pioneers
42024-01-07Chennai CheetahsDelhi Defenders
52024-01-07Lucknow LancersMumbai Monarchs
62024-01-10Jaipur JaguarsKolkata Krakens
72024-12-28Punjab PioneersLucknow Lancers
82025-01-04Mumbai MonarchsDelhi Defenders

Output

match_idmatch_dateday_name
32024-01-06Saturday
42024-01-07Sunday
52024-01-07Sunday
72024-12-28Saturday

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_id

Another 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_id

Another 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 →