*******
- *********************************************************************************
- **************************************************************;
- **********************************************************************
***********
- ************************************************** *******.
- *********************************************************************
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
SELECT
C.account_id,
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
GROUP BY 1;
Explanation
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