Select 10% Users Randomly from User Table

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.

Stable 10% Pseudo-Random User Sample

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.

Table users

column	type	constraints
id	INTEGER	PRIMARY KEY, NOT NULL
username	VARCHAR	NOT NULL
signup_date	DATE	NOT NULL

Sample data:

id	signup_date	username
1	2023-01-02	alice
2	2023-02-15	bob
3	2023-03-20	carol
4	2023-04-10	dave
5	2023-05-05	erin

Expected output

A deterministic pseudo-random sample containing CEIL(10% of users). For the sample data, ordering by MD5((id * 3)::text) puts user id 2 first, so one row is returned.

id	signup_date	username
2	2023-02-15	bob

Hints

1. Use a stable hash expression for reproducible pseudo-random ordering.
2. Use CEIL(COUNT(*) * 0.10) to choose at least one row when the table is small.