0

I need to calculate MRR as describe in https://www.chargebee.com/blog/mrr-subscription-businesses-saas-metrics-101/ between selected two dates using postgresql json, also my subscription are having intervals like "1 DAY", "15 DAY", "2 WEEK", "X WEEK", "X MONTH", "x YEAR" where x can be any number

I have following subscription schema and values:

CREATE TABLE subscriptions
(
  id bigserial NOT NULL,
  data json,
  created_date date,
  updated_date date,
  user_id bigserial NOT NULL,
  CONSTRAINT subscription_id_pk PRIMARY KEY (id)
)

And data are inside column "data" in json format like:

{
  "id": "1",
  "amount": 3900,
  "interval": "1 WEEK",
  "created": 1424011935
}

And transactions, is created when each recurring subscription is succeeded. I think transaction is not required, but if u can develop with this then good too.

CREATE TABLE transactions
(
  id bigserial NOT NULL,
  data json,
  created_date date,
  updated_date date,
  user_id bigserial NOT NULL,
  CONSTRAINT transactions_id_pk PRIMARY KEY (id)
)

And data are inside column "data" in json format like:

{
  "id": "1",
  "amount": 3900,
  "subscription_id": "1"
  "created": 1424011935

}

you can use created unix timestamp from data json or created_date from table whichever is better for you

  • here subscription created date is subscription created first time and not each recurring.

  • I am having version 9.3

Community
  • 1
  • 1
mukund
  • 2,253
  • 1
  • 18
  • 31
  • What is the relation between subsciption and transaction? Also, have you tried anything so far? – Jakub Kania Mar 22 '15 at 08:26
  • @JakubKania sorry forget to add transaction detail, have added. I think to calculate it only subscription table is enought. I tried to develop but can not generate interval calculation for recurring in subscription – mukund Mar 22 '15 at 11:26

1 Answers1

0

It's rather simple generate_series can be used with INTERVALs and we can use a LATERAL JOIN to generate the data for each json row.

WITH j AS(
SELECT '{
  "id": "1",
  "amount": 3900,
  "interval": "1 WEEK",
  "created": 1424011935
}'::json AS data
UNION ALL
SELECT '{
  "id": "2",
  "amount": 100,
  "interval": "23.5 DAY",
  "created": 552614400
}'::json
),
dates AS(
SELECT
'2008-03-01 00:00'::timestamp AS start
,'2015-03-08 07:00'::timestamp AS stop
)
SELECT j.data->>'id'
    ,sum((j.data->>'amount')::int)
    ,count(*) as intervals
FROM j
CROSS JOIN dates
CROSS JOIN LATERAL
    (SELECT i
    FROM generate_series(to_timestamp((j.data->>'created')::int), dates.stop + (j.data->>'interval')::interval,(j.data->>'interval')::interval) as i
    WHERE i BETWEEN dates.start AND dates.stop
    ) AS q
GROUP BY j.data->>'id'

The above gives us the result:

 id |  sum  | intervals
----+-------+-----------
 1  | 11700 |         3
 2  | 11000 |       110

SQLFiddle

Jakub Kania
  • 15,665
  • 2
  • 37
  • 47
  • Can u explain for first result, it shows interval 3 only, but week count is too many between 2008 & 2015 Also I wants results as follow: say I have a date: 2010-12-01 to 2015-03-01 Means some subscription may already started earlier to 2010-12-01 Say one subscription is started in 2008-12-02 so it should reflect in 2010-12-01 to 2015-03-01 in each month, I need group by each month or date, and if its year it should be yealyamount/12 pM and same for others too. please view reference link for more details – mukund Mar 23 '15 at 01:26
  • @mukund It's 3 because the first record has created timestamp as 15th February. And grouping by date part is a rather well known problem that have been answered in other questions. For example: http://stackoverflow.com/a/15691167/2115135 – Jakub Kania Mar 25 '15 at 18:55