P90 latency of each service

Hard

Question

The requests table has one row for each request that a service handled, with the service name and the latency_ms (how many milliseconds the request took).

The P90 latency of a service is the value that 90 percent of its requests are as fast as, or faster than. Use this exact rule: sort the latencies of the service from small to large, count the requests (N), and take the value at position 0.9 x N, rounded UP to a whole number. For example, N = 10 gives position 9, N = 7 gives 6.3 which is rounded up to position 7, and N = 1 gives position 1. No averaging between two values.

Return service and p90_ms (the P90 latency). The order of the rows does not matter.

percentile
window function
row number
count over
cume dist
integer division
group by

Read first: Lesson 48, SQL patterns that interviews repeat

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

requests3 columns · 38 rowsnot run yet

CREATE TABLE requests (id INTEGER PRIMARY KEY, service TEXT NOT NULL, latency_ms INTEGER NOT NULL);

Example rows of the requests table
id	service	latency_ms
1	search	220
2	search	120
3	search	900
4	search	150
5	search	175
6	search	140
7	search	1500
8	search	160
9	search	135
10	search	190
11	search	260
12	search	155
13	search	420
14	search	180
15	search	300
16	search	170
17	search	200
18	search	350
19	search	240
20	search	210
21	checkout	380
22	checkout	300
23	checkout	2000
24	checkout	450
25	checkout	320
26	checkout	400
27	checkout	350
28	login	60
29	login	95
30	login	50
31	login	400
32	login	60
33	login	75
34	login	55
35	login	80
36	login	65
37	login	70
38	maps	40

About this SQL

SQLite has no PERCENTILE_CONT, PERCENTILE_DISC or MEDIAN, so rank the rows yourself. Whole number / whole number cuts the decimals, so you can round a division up with whole numbers only: add the divisor minus 1 to the top, then divide. Do not rely on CEIL(), which a SQLite build may not have, and do not use ROUND(), which rounds 6.3 down to 6. CUME_DIST() gives the share of rows that are less than or equal to the current value, and it is another way to find the same position.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready