CODEWITHSUNDEEP
mysqlsqlpartition
Pravin
Pravin

Reputation: 471

Calculate new users subscription amount MySQL

I have a dataset where I need to find out New subscribers revenue.

These are subscribers that are paying either weekly or monthly depending on the subscription they are on.

The unique identifier is "customer" and the data is at timestamp level, but I want it rolled up at monthly level.

Now for each month, we need to find out revenue for only NEW subscribers. Basically, imagine customers being on monthly/weekly subscriptions and we only want their FIRST Payments to be counted here.

Here's a sample dataset and 

created            customer                  amount
16-Feb-18 14:03:55  cus_BwcisIF1YR1UlD  33300
16-Feb-18 14:28:13  cus_BpLsCvjuubYZAe  156250
15-Feb-18 19:19:14  cus_C3vT6uVBqJC1wz  50000
14-Feb-18 23:00:24  cus_BME5vNeXAeZSN2  162375
9-Feb-18 14:27:26   cus_BpLsCvjuubYZAe  156250

....and so on...

here is the final desired output

yearmonth new_amount
Jan - 2018   100000
Feb - 2018   2000
Dec - 2017   100002

This needs to be done in MySQL interface.

Upvotes: 0

Views: 440

Answers (2)

Prabha Rajan
Prabha Rajan

Reputation: 68

   We can have sql subquery for only the 1st payment of the new customer with   
   amount for every month and year

   The query is as follows

  SELECT month(created) as mm,year(created) as yyyy,
  sum(amount) as new_amount
  FROM t
  WHERE t.created=(select min(t2.created) from t t2 where 
    t2.customer=t.customer)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270613

Basically, you want to filter the data to the first customer. One method of doing this involves a correlated subquery.

The rest is just aggregating by year and month. So, overall the query is not that complicated, but it does consist of two distinct parts:

select year(created) as yyyy, month(created) as mm,
       count(*) as num_news,
       sum(amount) as amount_news
from t
where t.created = (select min(t2.created)
                   from t t2
                   where t2.customer = t.customer
                  )
group by yyyy, mm

Upvotes: 1

Related Questions