Hotel Search
ASKED IN INTERVIEWExpedia
Problem Statement
Expedia marketing teams want to tailor their advertisements based on regional preferences. Specifically, they want to know which destination city is most popular among users who reside in California.
Write an SQL query to find the destination city with the highest number of searches from California-based users.
Rules:
- Consider only users whose state is California.
- If multiple cities have the same maximum search count, return the city name that comes first alphabetically.
- Output columns: destination_city, search_count.
- Limit the output to the top 1 result.
Table Schema:
- Users: user_id, user_name, user_state.
- Searches: search_id, user_id, destination_city, search_timestamp.
Tests your understanding of
Joins, Aggregation, Filtering and Ranking
Input Tables
Users
| user_id(INTEGER) | user_state(VARCHAR) |
|---|---|
| 1 | California |
| 2 | New York |
| 3 | California |
| 4 | Texas |
| 5 | California |
| 6 | California |
Searches
| search_id(INTEGER) | user_id(INTEGER) | destination_city(VARCHAR) |
|---|---|---|
| 101 | 1 | Las Vegas |
| 102 | 3 | Las Vegas |
| 103 | 2 | Miami |
| 104 | 5 | Seattle |
| 105 | 6 | Las Vegas |
| 106 | 1 | Seattle |
Expected Output
| destination_city(VARCHAR) | search_count(INTEGER) |
|---|---|
| Las Vegas | 3 |
Tags
MediumASKED IN INTERVIEWJoinsAggregationFilteringRanking
10-15 min
64%