Table Schema
Inspect
Table
| Column | Type |
|---|---|
| query | varchar |
| search_date | date |
Trending
Search
Terms
Find queries whose frequency increased compared to the previous day.
Inspect
Sample Data
Input
Output
Sample Input: search_logs
| query | search_date |
|---|---|
| AI | 2024-01-01 |
| AI | 2024-01-02 |
| AI | 2024-01-02 |
| SQL | 2024-01-01 |
| SQL | 2024-01-02 |
| Python | 2024-01-02 |
| Python | 2024-01-02 |
Expected Output
| query |
|---|
| AI |
SQL Editor
Run
Query
Waiting for query
| query |
|---|
| AI |
Hints
Unlock
Clues
Hint 01: Count searches per query per day.
Hint 02: Use LAG to fetch the previous day's frequency.
Hint 03: Return queries where today's count is larger.
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.
WITH daily_counts AS (
SELECT query, search_date, COUNT(*) AS frequency
FROM search_logs
GROUP BY query, search_date
),
with_previous AS (
SELECT
query,
search_date,
frequency,
LAG(frequency) OVER (PARTITION BY query ORDER BY search_date) AS previous_frequency
FROM daily_counts
)
SELECT DISTINCT query
FROM with_previous
WHERE frequency > previous_frequency;Explanation
Step By
Step
01
Aggregate daily frequencies.
02
Compare each day with LAG.
03
Select queries with an increase over the previous day.
Related Questions