Question: Advertiser Status
"Given campaign start and end dates, identify advertisers who haven't had any active campaigns in the past 30 days and mark them as 'Inactive'."

Step-by-Step Explanation:

You are given a table called campaigns with:

• advertiser_id
• campaign_start_date
• campaign_end_date

You need to:

1. Find advertisers who have no active campaigns in the past 30 days.
2. Label them as 'Inactive'.
3. Advertisers who had an active campaign anytime in the past 30 days are 'Active'.

Sample Data (campaigns):

Assume today's date is 2024-02-20.

Step 1: Define the Past 30 Days

Today is 2024-02-20.
The past 30 days means the date range: 2024-01-21 to 2024-02-20.

We are checking:

• Did the advertiser have any campaign active during this period?

A campaign is active if:

• It started before today (campaign_start_date <= today)
• AND it ended after 30 days ago (campaign_end_date >= today - 30 days)

Step 2: Check if Campaign is Active

A campaign overlaps with the last 30 days if:

textCopyEditcampaign_start_date <= today
AND campaign_end_date >= today - 30 days

Step 3: Mark Active or Inactive

• If the advertiser has at least one campaign matching the above, they are Active.
• Otherwise, they are Inactive.

SQL Query:

WITH advertiser_activity AS (
  SELECT
    advertiser_id,
    CASE 
      WHEN MAX(
        CASE 
          WHEN campaign_start_date <= CURRENT_DATE()
           AND campaign_end_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
          THEN 1 ELSE 0
        END
      ) = 1 THEN 'Active'
      ELSE 'Inactive'
    END AS advertiser_status
  FROM 
    campaigns
  GROUP BY 
    advertiser_id
)

SELECT * 
FROM advertiser_activity;

Explanation of SQL:

• CASE WHEN campaign overlaps past 30 days THEN 1 ELSE 0:
  For each campaign, assign 1 if active, otherwise 0.
• MAX(...) over advertiser_id:
  • If any campaign is active, the max will be 1 (active).
  • If none are active, max will be 0 (inactive).
• GROUP BY advertiser_id:
  We group by advertiser to evaluate all their campaigns together.

Expected Output:

Important Points:

• CURRENT_DATE() gets today's date in SQL.
• DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) gets the date 30 days ago.
• If one advertiser has multiple campaigns, even one active campaign is enough to be 'Active'.
• Always group by advertiser.