CODEWITHSUNDEEP
sqlpostgresql
Ryan
Ryan

Reputation: 1

Is it possible to use dynamic values for a query with the BETWEEN keyword?

My PostgreSQL DB contains transactions for users who have yearly or monthly subscriptions. I want to get an active count of users on each day for an entire month. My schema contains columns with timestamp (in unix) and the type of plan a specific user has.

My thought process is querying the count of users in the past month with a monthly subscription and the same for the yearly subscribers, but in the past year.

For one day, the query is simple. To do this for each day in the month, however, I would have to dynamically update the values in the BETWEEN clause to get an accurate count. Is there a way to do this?

schema:

timestamp | user_email | event_name | plan_name | total_amount | overage_amount

Sample data (1 user):

timestamp email event_name plan_name total_amount overage_amount
1530417600 "[email protected]" "Signup" "Monthly" 0 0
1530460800 "[email protected]" "Trial" "Monthly" 0 0
1530720000 "[email protected]" "Recurring" "Monthly" 1000 0
1533398400 "[email protected]" "Recurring" "Monthly" 1100 100
1534694400 "[email protected]" "Upgrade" "Yearly" 9568 68
1542646800 "[email protected]" "Overage" "Yearly" 123 123
1553011200 "[email protected]" "Overage" "Yearly" 321 321
1566230400 "[email protected]" "Recurring" "Yearly" 10100 100
1597852800 "[email protected]" "Recurring" "Yearly" 10000 0
1597932000 "[email protected]" "Refund" "Yearly" -10000 0

Expected result:

Given a year and month, I want to generate the current amount of active users each day of that month and year.

[

   { dayOfMonth: 1, total: 50 }

            .

            .

            .

   {dayOfMonth: 30, total: 70}

]

Upvotes: 0

Views: 138

Answers (1)

Belayer
Belayer

Reputation: 14886

You do not need to dynamically update your values. Instead you can calculate/generate the dates for the year-month parameter using the make_date(...) and generate_series(...) functions then join your table converting your unix timestamp (assumption, you really should convert and store as actual timestamp). You wind up with something like: (see demo)

with parms( p_year, p_month) as 
     ( values (2018,08) )                --- given year, month
    -- with given year, month build each day for that period
   , dt_range (date_range) as 
     ( select generate_series ( make_date (p_year, p_month, 1)
                              , (make_date  (p_year, p_month, 1) + interval '1 month' - interval '1 day')::date
                              , interval '1 day'
                              )::date 
         from parms
     ) 
-- get date, daily_totalm daily_overage for each day in month
select d.date_range                    "Date" 
     , coalesce(sum(total_amount),0)   "Daily Total"
     , coalesce(sum(overage_amount),0) "Daily Overage"
  from dt_range  d 
  left join test t 
         on  to_timestamp(t.u_timestamp)::date = d.date_range
  group by d.date_range
  order by d.date_range;

Upvotes: 1

Related Questions