# Searches by Users With Email > One user's search behavior. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: ESPN ## Problem The search quality team is analyzing result effectiveness but only for identifiable users. Pull all search query details for users who have an email address on file, limited to queries that returned at least one result. ## Tables ### search_queries - query_id INTEGER - user_id INTEGER - search_term TEXT - results_count INTEGER - clicked_result INTEGER - query_time TEXT Sample rows (10 of 200): | query_id | user_id | search_term | results_count | clicked_result | query_time | | --- | --- | --- | --- | --- | --- | | 2071 | 100 | laptop deals | 17 | 2 | 2026-02-02 01:11:00 | | 2142 | 197 | phone case iphone 15 | 34 | 3 | 2026-03-03 02:22:00 | | 2213 | 294 | usb-c cable 6ft | 51 | NULL | 2026-04-04 03:33:00 | | 2284 | NULL | gaming mouse | 68 | 5 | 2026-05-05 04:44:00 | | 2355 | 488 | mechanical keyboard | 85 | 6 | 2026-06-06 05:55:00 | | 2426 | 585 | 4k monitor | 102 | NULL | 2026-07-07 06:06:00 | | 2497 | 682 | standing desk | 119 | 8 | 2026-08-08 07:17:00 | | 2568 | NULL | webcam 1080p | 136 | 9 | 2026-09-09 08:28:00 | | 2639 | 876 | bluetooth speaker | 153 | NULL | 2026-01-10 09:39:00 | | 2710 | 973 | smart watch | 170 | 1 | 2026-02-11 10:50:00 | ### users - user_id INTEGER - username TEXT - email TEXT - signup_date TEXT - account_status TEXT - age_bucket TEXT Sample rows (10 of 200): | user_id | username | email | signup_date | account_status | age_bucket | | --- | --- | --- | --- | --- | --- | | 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 | | 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 | | 294 | amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 | | 391 | arjun | arjun@example.com | 2025-05-05 | active | 55-64 | | 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ | | 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL | | 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 | | 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 | | 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 | | 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 | ## Expected output First 5 of 128 rows: | query_id | user_id | search_term | results_count | clicked_result | query_time | | --- | --- | --- | --- | --- | --- | | 2071 | 100 | laptop deals | 17 | 2 | 2026-02-02 01:11:00 | | 2142 | 197 | phone case iphone 15 | 34 | 3 | 2026-03-03 02:22:00 | | 2213 | 294 | usb-c cable 6ft | 51 | NULL | 2026-04-04 03:33:00 | | 2355 | 488 | mechanical keyboard | 85 | 6 | 2026-06-06 05:55:00 | | 2426 | 585 | 4k monitor | 102 | NULL | 2026-07-07 06:06:00 | ## Worked solution and explanation ### Why this problem exists in real interviews This problem targets self-join across the `search_queries` and `users` tables. You need to work with the `email` column to satisfy the requirements. --- ### Break down the requirements #### Step 1: Join `search_queries` to `users` The join connects the two tables on their shared key. This brings the columns needed for filtering and aggregation into a single row set. #### Step 2: Filter out null values Exclude rows where `email` is NULL. This prevents nulls from polluting aggregations or creating phantom groups. --- ### The solution **Join `search_queries` to `users` to find searches by users with email** ```sql SELECT sq.query_id, sq.user_id, sq.search_term, sq.results_count, sq.clicked_result, sq.query_time FROM search_queries sq JOIN users u ON sq.user_id = u.user_id WHERE u.email IS NOT NULL AND sq.results_count > 0 ``` > **Cost Analysis** > > With ~60M rows, the join cost depends on the smaller table's cardinality. An index on the filter/join columns would reduce the scan to a seek. > **Interviewers Watch For** > > Interviewers watch for how you handle NULL values and whether you account for them in filters and aggregations. > **Common Pitfall** > > Forgetting to filter NULLs creates phantom groups or inflated counts. Always check `null_fraction` in the schema before assuming columns are clean. --- ## Common follow-up questions - What result would you get if every value in `users.email` were NULL? Would your query return an empty set or something unexpected? _(Tests extreme NULL scenarios and whether the candidate guards against edge cases in `email`.)_ - If `search_queries` grew to contain billions of rows, which part of your query would become the bottleneck given the cardinality of `user_id`? _(Tests ability to identify performance hotspots related to `search_queries.user_id` at scale.)_ - How would you modify this query if the business logic required grouping by both `query_id` and `user_id` instead of just one? _(Tests ability to adapt the query structure to changing requirements.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/searches_by_users_with_email) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.