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

Question

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

Answer 1

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)

Answer 2

You could use something like this:

SELECT customer_id, signup_date

FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY signup_date) AS row_num
             , customer_id
             , signup_date
  FROM your_table
) -- AS sub
WHERE row_num = 3

Answer 3

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)

Answer 4

You could use

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

Answer 5

Is the sign up date always in ascending order? Generally, when table has large number of rows then Rank() function would work better. Rank() is faster than Row_Number(). My advice would be to only use subqueries when you need to get results quickly. If the query needs to be used everyday, reused, try to use a CTE for better readability and performance.

With CTE_Customer_Signup_Date AS (

SELECT Rank() OVER (ORDER BY signup_date) AS Rank
             , customer_id
             , signup_date
  FROM your_table

)

Select customer_id, signup_date from CTE_Customer_Signup_Date 
where Rank =3