How to write a query showing associated record growth, week after week?

user_149080 0
Viewed 31

Given the following two tables & fields:

companies (id, created_at, title)
company_members (id, company_id, created_at, member_info)

I would like to write a query that shows how the company_members grow over time, from the week the company was created, and then showing week by week until the current week.

Example of desired results:

Example of desired data

The example above should be showing, for example, that in Dec 6th, on Week 1 100 members were created. By Week 2 (there are now 110, 110% of members)

How can I get started on this?

postgresql sql
1 Answers

Here is a solution to calculate the growth over time, per company_id, from the week the company was created, and then showing week by week until the current week :

WITH list AS
(
SELECT c.id AS company_id
     , cm.created_at - mod((EXTRACT('dow' FROM cm.created_at) + 6) :: integer, 7) AS week -- first day of the week associated to cm.created_at
     , count(*) AS count
  FROM companies AS c
 INNER JOIN company_members AS cm
    ON cm.created_at >= c.created_at
 GROUP BY c.id, week
)
SELECT l.company_id
     , l.week
     , to_char(sum(l.count) OVER w / first_value(l.count) OVER w, '99999V99 %') AS growth
  FROM list AS l
 RIGHT JOIN 
     ( SELECT company_id
            , generate_series(min(week), now() :: date , interval '7 days') AS week
         FROM list
        GROUP BY company_id
     ) AS w
    ON w.company_id = l.company_id
   AND w.week = l.week
WINDOW w AS (PARTITION BY l.company_id ORDER BY l.week)
 ORDER BY l.company_id, l.week

see the test result in dbfiddle.

The way you want to display the result is your question is not clear for the moment.

user_80600170
Related