I would like to count all customers and return the signup date for the 3rd customer who has signed up.
Essentially evaluate the number of customers that signed up and once the count of customers that have signed up reaches 3 to return the signup date and the id of the 3rd customer
sample table
customer_id signup_date 3993 2019-01-01 9392 2019-01-02 2143 2019-01-03 8372 2019-01-04
output table
customer_id signup_date 2143 2019-01-03
Advertisement
Answer
Use row_number() to filter needed value:
row_number()→ bigint
Returns a unique, sequential number for each row, starting with one, according to the ordering of rows within the window partition.
-- sample data
WITH dataset (customer_id, signup_date ) AS (
VALUES (3993, date '2019-01-01'),
(9392, date '2019-01-02'),
(2143, date '2019-01-03'),
(8372, date '2019-01-04')
)
--query
select customer_id, signup_date
from (
select *,
row_number() over(order by signup_date) rn
from dataset
)
where rn = 3
Output:
| customer_id | signup_date |
|---|---|
| 2143 | 2019-01-03 |
Note that in case of matching dates (i.e. several rows having the same date) return value is undefined so you will need to apply extra ordering (for example by id – row_number() over(order by signup_date, customer_id) rn) (or maybe look into using combination of rank and row_number to return multiple results)