topic Re: How to count the number of campaigns per day based on the start and end dates of the campaigns in SQL Spark Databrick in Data Engineering

How to count the number of campaigns per day based on the start and end dates of the campaigns in SQL Spark Databrick

dulu — Sat, 03 Dec 2022 09:01:27 GMT

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

Input Table:

Out needed (result):

How do I need to write the SQL command in databricks to get the above result? thanks all

Re: How to count the number of campaigns per day based on the start and end dates of the campaigns in SQL Spark Databrick

UmaMahesh1 — Sat, 03 Dec 2022 10:11:02 GMT

Hi @Du Lu

Step1 : Collect all the dates between start date and end date as the campaign is happening on those dates.

Step2; Explode the collected date set

Step3: Count the number of campaigns happening on a particular date using a simple grouping expression.

Query looks something like this : (assuming startDate and EndDate are date columns and not strings. If strings, cast to date before proceeding).

select count(campaignName) as CampaignCount , EventDate from

( select campaignName, explode(sequence(StartDate, EndDate, interval 1 day)) as EventDate from source)

group by 2

If you want specific date range you can do that by adding a where clause in the above query.

select count(campaignName) as CampaignCount , EventDate from

( select campaignName, explode(sequence(StartDate, EndDate, interval 1 day)) as EventDate from source)

where EventDate between "2022-07-06" and "2022-08-03"

group by 2

Hope this helps...

Cheers.

Re: How to count the number of campaigns per day based on the start and end dates of the campaigns in SQL Spark Databrick

Hubert-Dudek — Sat, 03 Dec 2022 20:26:18 GMT

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`;

Re: How to count the number of campaigns per day based on the start and end dates of the campaigns in SQL Spark Databrick

boyelana — Sat, 10 Dec 2022 09:04:06 GMT

Nice query...