Table Schema
Inspect
Table
| Column | Type |
|---|---|
| search_id | integer |
| user_id | integer |
| source | varchar |
| destination | varchar |
| search_time | date |
Popular
Routes
Find the most frequently searched route.
Inspect
Sample Data
Input
Output
Sample Input: search_routes
| search_id | user_id | source | destination | search_time |
|---|---|---|---|---|
| 1 | 101 | A | B | 2024-01-01 |
| 2 | 102 | A | B | 2024-01-01 |
| 3 | 103 | B | C | 2024-01-01 |
| 4 | 104 | A | B | 2024-01-02 |
| 5 | 105 | B | C | 2024-01-02 |
| 6 | 106 | A | D | 2024-01-02 |
| 7 | 107 | A | B | 2024-01-03 |
| 8 | 108 | B | C | 2024-01-03 |
| 9 | 109 | A | B | 2024-01-03 |
Expected Output
| source | destination | count |
|---|---|---|
| A | B | 5 |
SQL Editor
Run
Query
Waiting for query
| source | destination | count |
|---|---|---|
| A | B | 5 |
Hints
Unlock
Clues
Hint 01: A route is the source plus destination pair.
Hint 02: Count searches per pair.
Hint 03: Sort descending and keep the top row.
Solution
Locked
Answer
Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.
SELECT source, destination, COUNT(*) AS count FROM search_routes GROUP BY source, destination ORDER BY count DESC LIMIT 1;
Explanation
Step By
Step
01
Group searches by route.
02
Count each route search.
03
Order by frequency and return the most searched route.
Related Questions