Select 10% Users Randomly from User Table
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
users
+----+----------+-------------+
| id | username | signup_date |
+----+----------+-------------+
| 1 | alice | 2023-01-02 |
| 2 | bob | 2023-02-15 |
| 3 | carol | 2023-03-20 |
| 4 | dave | 2023-04-10 |
| 5 | erin | 2023-05-05 |
+----+----------+-------------+
##### Scenario
Need to pull an exploratory sample from a large user table for downstream analysis.
##### Question
Write an SQL query that randomly selects approximately 10% of all users from the users table.
##### Hints
Use a built-in random function or TABLESAMPLE and filter by a threshold.
Overview: This question evaluates competency in randomized sampling and data manipulation using SQL or Python, focusing on probabilistic selection and the use of built-in sampling functions within the Data Manipulation (SQL/Python) domain.
Write a PostgreSQL query that returns a reproducible pseudo-random sample of approximately 10% of rows from the `users` table. For this exercise, order users by `MD5((id * 3)::text)` as the stable pseudo-random key, then return `CEIL(COUNT(*) * 0.10)` users. Return `id`, `username`, and `signup_date`.
Tables
users(id INTEGER, username VARCHAR, signup_date DATE)
Hints
- Use a stable hash expression for reproducible pseudo-random ordering.
- Use `CEIL(COUNT(*) * 0.10)` to choose at least one row when the table is small.