189. Campaign activation date

medium twitter


  • *********************************************************************************
  • **************************************************************;
  • **********************************************************************


  • ************************************************** *******.
  • *********************************************************************

Table 1: twitter_campaigns

List of advertising campaigns created by an ad account on Twitter.

   col_name   | col_type
 account_id   | bigint
 campaign_id  | bigint
 campaign_type| varchar(30)

Table 2: twitter_campaign_spend

Daily ad campaign spends by Twitter's advertiser accounts.

   col_name   | col_type
 campaign_id  | bigint
 date         | date
 spend        | float

Sample results

 account_id | start_date
       8006 | 2022-01-05
       8010 | null

Solution postgres

        MIN(S.date) AS start_date
FROM twitter_campaigns C
LEFT JOIN twitter_campaign_spend S
ON C.campaign_id = S.campaign_id
AND S.spend > 0


This query retrieves data from two tables, "twitter_campaigns" and "twitter_campaign_spend," and joins them together based on the campaign_id column. The "LEFT JOIN" ensures that all the data from the twitter_campaigns table is included in the results, even if there is no matching data in the twitter_campaign_spend table.

The "AND S.spend > 0" condition filters out any rows in the twitter_campaign_spend table where the spend is less than or equal to 0.

The "GROUP BY 1" statement groups the results by the account_id column, and the MIN(S.date) function returns the earliest date from the twitter_campaign_spend table for each account_id.

Overall, this query is trying to find the start date of each campaign for each account, using the earliest date when there was a spend greater than 0 in the twitter_campaign_spend table.

Expected results

More Twitter questions

ID Title Level FTPR
190 Twitter campaign spend report twitter hard
191 Number of replies by group twitter medium
192 Number of replies to the original tweet by group twitter medium
193 Difference of replies by testing groups twitter medium