[SOLVED] Dynamic SQL query which calculates the revenue generated for first X days since the onboarded Date

Dynamic SQL query which calculates the revenue generated for first X days since the onboarded Date

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

Solution

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