## Problem Given a table of page visits: ```sql CREATE TABLE visits ( user_id BIGINT, visit_date DATE, page VARCHAR(100) ); ``` Return all `user_id` values that visited the site on exactly one distinct date (i.e., they never returned). Order by `user_id` ascending. ## Example ``` visits: user_id | visit_date | page 1 | 2024-01-01 | /home 1 | 2024-01-03 | /products 2 | 2024-01-01 | /home 3 | 2024-01-02 | /home 3 | 2024-01-02 | /cart <- same date, different page Output: user_ids 2 and 3 (user 3 visited twice on the same day, but only 1 distinct date) ``` ## Follow-ups 1. How would you modify the query to also include users who only returned once (exactly 2 distinct visit dates)? 2. What index would you add to make this query fast on a 1B-row table? 3. Non-returning users within a specific cohort (first visit in January 2024 only) -- how do you filter? 4. Write this in Python using pandas `groupby` on the same data.

InterviewDB Question

Non-Returning Users - Identify Users Who Never Made a Second Visit

phone

Question Details

Problem Given a table of page visits: Return all user_id values that visited the site on exactly one distinct date (i.e., they never returned). Order by user_id ascending. Example Follow-ups How would you modify the query to also include users who on…

Full Details

🔒

Unlock full leaked content

Full insider details, leaked discussions, and candidate experiences.

Get Premium →

Topics

Coding Onsite Phone

More from Amazon

Reddit 175 k remote offer or 300 k in Bay Area Reddit Done with 3 rounds of On-site Amazon SDE 1 interview , but no communication after that. What to do? Reddit Nned direction for future prepration. Reddit Have an Amazon. SDE1 interview in 4 days, need tips Reddit After many years in the industry, I still struggle with textbook definitions in interviews

← All Amazon questions Amazon Software Engineer Questions → All Hash Table Questions →