Count all records and output values once a condition is met [SQL]-CodePudding

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

CodePudding user response：

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)

CodePudding user response：

select * from (
  select 
    customer_id, 
    signup_date, 
    rank() over (order by signup_date) 
  from signups
) sub where sub.rank = 3;

 customer_id | signup_date | rank 
------------- ------------- ------
        2143 | 2019-01-03  |    3
(1 row)

CodePudding user response：

You could use

select customer_id, signup_date
  from SampleTable
 order by signup_date
offset 2
 fetch next 1 rows