Search Query System - SQL Schema and Query Engine for Full-Text Search Logs

Question

## Round 1 - SQL

## Problem

You have a search query log system with the following schema:

```sql
CREATE TABLE searches (
    search_id    BIGINT PRIMARY KEY,
    user_id      BIGINT,
    query        VARCHAR(500),
    searched_at  TIMESTAMP,
    result_count INT
);

CREATE TABLE clicks (
    click_id    BIGINT PRIMARY KEY,
    search_id   BIGINT REFERENCES searches(search_id),
    result_rank INT,
    clicked_at  TIMESTAMP
);
```

**Task 1:** Find the top 10 most searched queries in the last 7 days, including their average result count.

**Task 2:** For each user, find their most recent search query. Return user_id and query.

**Task 3:** Compute the click-through rate (CTR) per query: CTR = clicks / total searches for that query. Only include queries searched at least 100 times.

```sql
-- Task 3 skeleton:
SELECT
    s.query,
    COUNT(DISTINCT s.search_id)            AS total_searches,
    COUNT(c.click_id)                      AS total_clicks,
    COUNT(c.click_id) * 1.0
        / COUNT(DISTINCT s.search_id)      AS ctr
FROM searches s
LEFT JOIN clicks c ON c.search_id = s.search_id
GROUP BY s.query
HAVING COUNT(DISTINCT s.search_id) >= 100
ORDER BY ctr DESC;
```

## Follow-ups

1. How do you find queries where users never clicked any result (zero CTR)?
2. Write a query to find the average rank of clicked results per query.
3. How would you detect duplicate searches by the same user within 5 minutes?
4. How would you partition this table for time-based query performance?

Search Query System - SQL Schema and Query Engine for Full-Text Search Logs

Question Details

Full Details

Topics

More from Pinterest