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?

Answer

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 ...