CODEWITHSUNDEEP
mysqlsql
Maths12
Maths12

Reputation: 981

How can I extract customers who have transacted since a specific date? (active customers definition)

All,

My definition of an active client is anyone who has transacted since November 2019. I am unsure how i can do this. e.g.

select 
customerid, 
sum(count) as transaction_count,
sum(value) as dollar_amount

from 
cust_orders

my table above is at transaction level e.g. transaction_id = 1, 2... etc..I roll it up at customer level but i only want customers who have transacted at least once since November 2019. I don't think it is as simple as adding: 'where yearmonth > 201911'. since e.g. if I have one customer who hasn't transacted since then i don't want any of their aggregated stats above. And if i have had customers who have transacted since november 2019, i do want their transaction counts prior to this included. perhaps i could do:

select 
customerid, 
sum(count) as transaction_count,
sum(value) as dollar_amount

from 
cust_orders where customerid in (select 

distinct

customerid 

from cust_orders where yearmonth > 201911))
)

does this make sense?

Upvotes: 0

Views: 292

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

First, you need aggregation in your first query. Second, you can use a HAVING clause to identify the active customers:

select customerid, 
       sum(count) as transaction_count,
       sum(value) as dollar_amount
from cust_orders 
group by customerid
having max(yearmonth) >= 201911;

Note: I interpret "since Nov 2011" as including that month, so I changed the comparison to >=.

Upvotes: 2

Related Questions