How to count the number of campaigns per day based on the start and end dates of the campaigns

Question

I need to count the number of campaigns per day based on the start and end dates of the campaigns

Columns: Campaign Name, Start Date, End Date

How do I need to write the SQL command in databricks?

Answer 1

Just create an array with sequence, explode it, and then group and count:

WITH cte AS
(SELECT
  `campaign name`, 
  explode(sequence(`Start date`, `End date`, interval 1 day)) as `Date`
FROM
  `campaigns`)
SELECT
  Count(`campaign name`) as `count unique campaigns`,
  `Date`
FROM
  cte
GROUP BY
  `Date`;