1

Consider the following datatables.
1. Customer Table

id name onboarded_date
1 xxxx 2021-10-01
2 yyyy 2021-10-02
3 zzzz 2021-10-05

2. Revenue Table

id customer_id date revenue
1 1 2021-10-01 100
1 2 2021-10-02 300
3 2 2021-10-03 200
4 3 2021-10-07 100
5 2 2021-10-10 100
6 3 2021-10-12 300
7 3 2021-10-14 600

How can we write a single dynamic query which calculates for each customer - the revenue generated for first 10 days from onboarded date (considering onboarded date as the start date)

Note: The start date and end date for each customer to calculate the total revenue is dynamic here

Expected Result:

id name start_date end_date total_revenue
1 xxxx 2021-10-01 2021-10-10 100
2 yyyy 2021-10-02 2021-10-11 600
3 zzzz 2021-10-05 2021-10-14 1000
Dinesh Chandran
  • 13
  • 5
  • 1
    Join by customer_id and date between onboarded_date and onboarded_date plus the period length, then aggregate? – Akina Oct 25 '21 at 06:10

1 Answers1

1

Instead of dynamical query you may use one with a parameter.

select c.id,
       c.name,
       c.onboard_date start_date,
       date_format(c.onboard_date + DAYS, '%Y-%m-%d') end_date,
       sum(revenue) revenue
  from customers c
  join revenue r
    on r.customer_id = c.id
 where r.date between c.onboard_date and c.onboard_date + DAYS
 group by c.id,
       c.name,
       c.onboard_date;

In your code you can just replace "DAYS" with some binding parameter.

See the dbfiddle for details

ekochergin
  • 4,109
  • 2
  • 12
  • 19