Your question is Remove Duplicate Daily Search Queries. Start with the requirements and the one table on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
You are given a daily search log from TELUS Digital Search and need to clean duplicate search events. Write a PostgreSQL query that identifies duplicate rows for the same user_id, search_query, and calendar day, keeps the earliest event for each duplicate set, and removes the rest from the table.
Treat rows with the same user, same exact search text, and same date portion of searched_at as duplicates. Ignore rows where search_query is NULL or an empty string. Your result should show which rows would be deleted before the delete is executed.
| Column | Type | Description |
|---|---|---|
| log_idPK | INT | Primary key for each search log row |
| user_id | INT | Identifier of the user who performed the search |
| search_query | VARCHAR(255) | Raw search text entered by the user |
| searched_at | TIMESTAMP | Timestamp when the search event occurred |
| surface | VARCHAR(100) | TELUS Digital product surface where the search was performed |