- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-03-2022 01:01 AM
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
- Labels:
-
Databricks SQL
-
SQL
-
SQL Spark Databrick
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-03-2022 02:11 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-03-2022 02:11 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-03-2022 12:26 PM
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`;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-10-2022 01:04 AM
Nice query...

