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 |
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