Same Opponent for First and Last Match of a Day

Problem

Table: `matches`

| Column Name | Type |
|---|---|
| home_id | int |
| away_id | int |
| played_at | datetime |

`(home_id, away_id, played_at)` is the primary key. Each row is a chess match between two players at a moment in time.

A player participates as either the home or away side. For each player and each calendar day they played, look at their earliest match and their latest match that day. Report the `player_id` of every player whose first and last match on at least one day were against the same opponent. Return each qualifying player once, in any order, in a column named `player_id`. Assume no player has two matches at the exact same timestamp on the same day.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 3 rows with the columns player_id.

Starter code (SQL)

SELECT *
FROM matches;

Related SQL questions

• 3-Consecutive-Week Marketing StreakHard
• 30-Day Rolling Average Trading VolumeHard
• 7-Day Active User Rolling MetricHard
• 7-Day Revenue Moving AverageHard
• Always Mid-Pack BakersHard
• Annual Energy Output Growth by Panel ModelHard