Marketing Campaign Duration.
EasyUnlock detailed company stats for this questionUpgrade
You are given 3 tables, campaign, user and transaction with the following columns
campaign table:
user table:
transaction table
Write a SQL query that calculates the average total duration of campaigns.
(Note: a campaign may span multiple date ranges across rows. Sum the total duration per campaign first, then average.)
SQL-- SQLite (which is what the editor is using)
SELECT AVG(total_duration) AS average_duration
FROM (
SELECT upsell_campaign_id,
SUM(julianday(date_end) - julianday(date_start)) AS total_duration
FROM campaign
GROUP BY upsell_campaign_id
);
-- MySQL
SELECT AVG(total_duration) AS avg_campaign_length
FROM (
SELECT upsell_campaign_id,
SUM(DATEDIFF(date_end, date_start)) AS total_duration
FROM campaign
GROUP BY upsell_campaign_id
) AS campaign_totals;
-- PostgreSQL
SELECT AVG(total_duration) AS avg_campaign_length
FROM (
SELECT upsell_campaign_id,
SUM(date_end - date_start) AS total_duration
FROM campaign
GROUP BY upsell_campaign_id
) AS campaign_totals;