CODEWITHSUNDEEP
sqlgoogle-bigquery
Hctor Alonso Hormazbal Vildsol
Hctor Alonso Hormazbal Vildsol

Reputation: 331

How to give names to output tables?

I need to do a JOIN using two ouput-tables (these tables result from previous operations using other tables). I want to give them specific names to reference them. I've try ALIAS, AS, INTO, but nothing works. I am new in SQL and I'd really like some help here. My code is the following

WITH SENDS AS (SELECT "CL" AS Country, 
               campaign_id AS ID_Campaign, 
               FORMAT_DATE("%m/%d/%Y",DATE(event_time)) AS Launch_date,
               COUNT(campaign_id) AS Sents
               FROM `linio-bi.marketing_emarsys_legacy.email_sends_783270611`
               WHERE EXTRACT(YEAR FROM DATE(event_time)) >= 2019
               GROUP BY ID_Campaign,Launch_date),

    BOUNCES AS (SELECT "CL" AS Country, 
               campaign_id AS ID_Campaign, 
               FORMAT_DATE("%m/%d/%Y",DATE(event_time)) AS Launch_date,
               COUNT(campaign_id) AS Bounces
               FROM `linio-bi.marketing_emarsys_legacy.email_bounces_783270611`
               WHERE EXTRACT(YEAR FROM DATE(event_time)) >= 2019
               GROUP BY ID_Campaign,Launch_date),

    OPENS AS (SELECT "CL" AS Country, 
               campaign_id AS ID_Campaign, 
               FORMAT_DATE("%m/%d/%Y",DATE(event_time)) AS Launch_date,
               COUNT(campaign_id) AS Opens
               FROM `linio-bi.marketing_emarsys_legacy.email_opens_783270611`
               WHERE EXTRACT(YEAR FROM DATE(event_time)) >= 2019
               GROUP BY ID_Campaign,Launch_date),
    
    COMPLAINED AS(SELECT "CL" AS Country, 
               campaign_id AS ID_Campaign, 
               FORMAT_DATE("%m/%d/%Y",DATE(event_time)) AS Launch_date,
               COUNT(campaign_id) AS Complaints
               FROM `linio-bi.marketing_emarsys_legacy.email_complaints_783270611`
               WHERE EXTRACT(YEAR FROM DATE(event_time)) >= 2019
               GROUP BY ID_Campaign,Launch_date),

    UNSUSCRIBED AS (SELECT "CL" AS Country, 
               campaign_id AS ID_Campaign, 
               FORMAT_DATE("%m/%d/%Y",DATE(event_time)) AS Launch_date,
               COUNT(campaign_id) AS Unsubscribed
               FROM `linio-bi.marketing_emarsys_legacy.email_unsubscribes_783270611`
               WHERE EXTRACT(YEAR FROM DATE(event_time)) >= 2019
               GROUP BY ID_Campaign,Launch_date),

    CLICKS AS (SELECT "CL" AS Country, 
               campaign_id AS ID_Campaign, 
               FORMAT_DATE("%m/%d/%Y",DATE(event_time)) AS Launch_date,
               COUNT(campaign_id) AS Clicks
               FROM `linio-bi.marketing_emarsys_legacy.email_clicks_783270611`
               WHERE EXTRACT(YEAR FROM DATE(event_time)) >= 2019
               GROUP BY ID_Campaign,Launch_date)
   

-- TABLE 1
SELECT A.Country,
A.ID_Campaign,
A.Launch_date, 
IFNULL(A.Sents,0) Sents, 
IFNULL(B.Bounces,0) Bounces,
IFNULL(C.Opens, 0) Opens,
IFNULL(D.Complaints,0) Complaints,
IFNULL(E.Unsubscribed,0) Unsubscribed,
IFNULL(F.Clicks,0) Clicks
FROM SENDS A
LEFT JOIN BOUNCES       B ON B.ID_Campaign = A.ID_Campaign AND B.Launch_date  = A.Launch_date 
LEFT JOIN OPENS         C ON C.ID_Campaign = A.ID_Campaign AND C.Launch_date  = A.Launch_date 
LEFT JOIN COMPLAINED    D ON C.ID_Campaign = A.ID_Campaign AND D.Launch_date  = A.Launch_date 
LEFT JOIN UNSUSCRIBED   E ON C.ID_Campaign = A.ID_Campaign AND E.Launch_date  = A.Launch_date 
LEFT JOIN CLICKS        F ON C.ID_Campaign = A.ID_Campaign AND F.Launch_date  = A.Launch_date 
ORDER BY A.ID_Campaign,A.Launch_date;

-- TABLE 2
SELECT "CL" AS Country, 
T.campaign_id AS ID_Campaign,
T.name AS Campaign_name,
CONCAT(CAST(T.campaign_id as STRING) ,'-',T.name) full_campaign
FROM(SELECT T.*,
  ROW_NUMBER() OVER (PARTITION BY campaign_id ORDER BY event_time DESC) as seqnum
  FROM `linio-bi.marketing_emarsys_legacy.email_campaigns_v2_783270611` T
) T
WHERE seqnum = 1

I'd like to make a LEFT JOIN between Table 1 and Table 2 on ID_Campaing, but also I would like to give them names,and of course I'd also want to give a name to the output of this JOIN. Any hints?

Upvotes: 0

Views: 258

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562891

You seem so adept with common table expressions, it's puzzling why you haven't taken the final step to make the last two into CTE's:

WITH SENDS AS (...),
    BOUNCES AS (...),
    OPENS AS (...),
    COMPLAINED AS(...),
    UNSUSCRIBED AS (...),
    CLICKS AS (...),
    -- now make two more CTE's:
    TABLE1 AS (
        SELECT A.Country,
        A.ID_Campaign,
        A.Launch_date,
        IFNULL(A.Sents,0) Sents,
        IFNULL(B.Bounces,0) Bounces,
        IFNULL(C.Opens, 0) Opens,
        IFNULL(D.Complaints,0) Complaints,
        IFNULL(E.Unsubscribed,0) Unsubscribed,
        IFNULL(F.Clicks,0) Clicks
        FROM SENDS A
        LEFT JOIN BOUNCES       B ON B.ID_Campaign = A.ID_Campaign AND B.Launch_date  = A.Launch_date
        LEFT JOIN OPENS         C ON C.ID_Campaign = A.ID_Campaign AND C.Launch_date  = A.Launch_date
        LEFT JOIN COMPLAINED    D ON C.ID_Campaign = A.ID_Campaign AND D.Launch_date  = A.Launch_date
        LEFT JOIN UNSUSCRIBED   E ON C.ID_Campaign = A.ID_Campaign AND E.Launch_date  = A.Launch_date
        LEFT JOIN CLICKS        F ON C.ID_Campaign = A.ID_Campaign AND F.Launch_date  = A.Launch_date
        ORDER BY A.ID_Campaign,A.Launch_date
    ),
    TABLE2 AS (
        SELECT "CL" AS Country,
        T.campaign_id AS ID_Campaign,
        T.name AS Campaign_name,
        CONCAT(CAST(T.campaign_id as STRING) ,'-',T.name) full_campaign
        FROM(SELECT T.*,
          ROW_NUMBER() OVER (PARTITION BY campaign_id ORDER BY event_time DESC) as seqnum
          FROM `linio-bi.marketing_emarsys_legacy.email_campaigns_v2_783270611` T
        ) T
        WHERE seqnum = 1)

-- and finally join them
SELECT ...
FROM TABLE1 JOIN TABLE2 ON ...

Upvotes: 1

Related Questions