Cube.dev - how to have the parameter "today date" in rolling window?

Question

I have a PostgreSQL database in which users can order from a start date to an end date. I want to know, for each day, how many users would be able to order.

Let's make an example, given:

id	from	to
A	2023-01-01	2023-01-03
B	2023-01-02	2023-01-07
C	2023-01-02	2023-01-09
D	2023-01-10	2023-01-12

For the first two weeks (let's suppose that the 01/01/2023 were on Monday), I would have (the third column is not needed):

day	n_of_users_that_can_order	who
2023-01-01	1	A
2023-01-02	3	A, B, C
2023-01-03	3	A, B, C
2023-01-04	2	B, C
2023-01-05	2	B, C
2023-01-06	2	B, C
2023-01-07	2	B, C
2023-01-08	1	C
2023-01-09	1	C
2023-01-10	1	D
2023-01-11	1	D
2023-01-12	0
2023-01-13	0
2023-01-14	0

My end result should be the above table aggregated per week:

week	total
2023-01-01 to 2023-01-07	15
2023-01-08 to 2023-01-14	4

I don't know how to do it with cube.dev, this is the idea I had for now:

ube(`Users`, {
  sql: `SELECT * FROM public.users`,

  measures: {
    usersPerDayThatCanOrder: {
      type: `count`,
      sql: `id`,
      rollingWindow: {
        trailing: `1 day`,
        offset: `start`
      },
      filters: [
        { sql: `${CUBE}.can_order_from >= ${TODAY} AND ${CUBE}.can_order_to <= ${TODAY}` }, // NOTE: today doesn't exist
      ]
    }
  },

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true
    },

    canOrderFrom: {
      sql: `can_order_from`,
      type: `time`
    },

    canOrderTo: {
      sql: `can_order_to`,
      type: `time`
    },
  },

  dataSource: `default`
});

But it does not work because I do not know how to have the real ${TODAY} value and also how to aggregate per week.

Answer 1

Here is a sql query which provides your expected result assuming that your table is named test :

SELECT d.week AS "week start"
     , (d.week + interval '6 days') :: date AS "week end"
     , sum( upper(daterange(d.week, (d.week + interval '1 week') :: date, '[)') * daterange(t."from", t."to", '[]'))
          - lower(daterange(d.week, (d.week + interval '1 week') :: date, '[)') * daterange(t."from", t."to", '[]'))
          )
  FROM
     ( SELECT generate_series(min(date_trunc('week', "from")), max("to"), interval '1 week') :: date AS week
         FROM test
     ) AS d
 INNER JOIN test AS t
    ON daterange(d.week, (d.week + interval '1 week') :: date, '[)') && daterange(t."from", t."to", '[]')
 GROUP BY d.week
 ORDER BY d.week

• The subquery calculates the weeks start date covered by table test
• The INNER JOIN clause intersects the weeks with the user date ranges
• Then rows are concatenated by weeks and the number of days are summed for all the users

By the way, the 2023/01/01 seems not to be Monday but Sunday.

Result :

week start	week end	sum
2022-12-26	2023-01-01	1
2023-01-02	2023-01-08	15
2023-01-09	2023-01-15	4

see test result in dbfiddle