Query To Get Orders By First Time Customers By A Given Date

Question

We have an order table like this.

   |OrderID| CustomerID|OrderedDate|
   |-------|----------|-----------| 
   |1      | 1000      |2024-02-27 20:00:00|
   |2      | 2000      |2024-02-28 23:00:00|
   |3      | 3000      |2024-02-28 23:01:00|
   |4      | 1000      |2024-02-29 23:01:00|
   |5      | 4000      |2024-02-29 23:02:00|

How do we get the list of customers that placed an order for the first time after a given date?

In the example above, trying to get all the customers who placed their first order on or after 2024-02-28 23:00:00. In this case, I am hoping to see 2000, 3000, 4000 as the results but not 1000 - because that is not a first time customer, by that date.

It seems like a simple enough task, but it is confusing when I attempt CROSS APPLY, RANK etc.

Desired result:

CustomerId
2000
3000
4000

Answer 1

In the example above, trying to get all the customers who placed their first order on or after 2024-02-28 23:00:00.

Your desired result is just a list of customer ids. Start with that. That means you need to do some sort of grouping, or use 'Distinct' since you want one row per customer instead of one row per order.

select CustomerId
from [Order]
group by CustomerId

Here you care about the first order, so you can use min(OrderedDate) to find that and report it for each CustomerId:

select CustomerId, min(OrderedDate) as FirstOrderedDate
from [Order]
group by CustomerId

You can use a HAVING clause much like a WHERE clause to filter grouped results based on aggregate functions:

select CustomerId
from [Order]
group by CustomerId
having min(OrderedDate) >= '2024-02-28 23:00:00'

HAVING is much like WHERE, but WHERE filters individual rows before grouping, HAVING filters the grouped results after the grouping and only works with aggregate functions. For instance if you wanted all customers that have only placed a single order and show the date of the order it would look like this (min or max works since there will be a single order):

select CustomerId, min(OrderedDate) as OrderedDate
from [Order]
group by CustomerId
having count(*) = 1

Answer 2

You could use a sub-query or CTE, but that requires two calls to the table. Below are two options, one being a CTE, while the 2nd option uses a windows function and should be more efficient.

--Using CTE, but calls table twice (less efficient)
with first_orders as (
  select customerId, min(orderedDate) as min_dt
  from orders
  group by customerId
  )
select o.*
  from orders o
  join first_orders f
    on o.customerId = f.customerId
where f.min_dt >= '2024-02-28';

orderId	customerId	orderedDate
2	2000	2024-02-28
3	3000	2024-02-28
5	4000	2024-02-29

--Using windows function, calls table just once
select 
 orderId, customerId, orderedDate
from (
  select *, 
   min(orderedDate) over (partition by customerId) as min_dt
  from orders
  )z
where min_dt >= '2024-02-28'
order by customerId;

orderId	customerId	orderedDate
2	2000	2024-02-28
3	3000	2024-02-28
5	4000	2024-02-29

fiddle

Answer 3

This is not tested,

Select * from(
SELECT *,Row_Number()over(partition by Customerid order by orderdate)rownum FROM Orders WHERE OrderDate > '2024-02-28 23:00:00')
)t where rownum=1



Select * from(
    SELECT *,Row_Number()over(partition by Customerid order by orderdate)rownum FROM Orders )
    )t where rownum=1 and OrderDate > '2024-02-28 23:00:00'