BigQuery - Cannot use DISTINCT while using UNION ALL & STRUCT with different data types

1
votes

I have 3 tables (green highlighted) and want to UNION ALL, LEADS and CONTACTS with each having 1 column not present (red highlighted). Also JOIN, CONTACTS table with ACCOUNTS to get 3 fields (so red highlighted field "code_uk" would be another column not present in LEADS after JOINING. Remaining columns I want to place one below the other (UNION ALL), as arranged in query.

Output Required:

  1. UNION ALL, LEADS and CONTACTS by arranging columns as done in query below - DONE
  2. Rows from CONTACTS where contact_email/contact_product is not present in LEADS. i.e. yellow highlighted rows 9,11,14 should not be present. - DONE
  3. Use STRUCT since there is mismatch in # of columns in LEADS & CONTACTS - DONE

  4. Only unique "lead_email" i.e. blue highlighted rows 2&5, from LEADS should not be present. When I use distinct (lead_email), error is type STRUCT cannot be used in SELECT DISTINCT

    • When I remove "distinct" just to test whether STRUCT works correctly or not, it outputs 15 rows but I want 13 rows (excluding 2&5).
    • Tried "group by" but it is also not working

Can someone please help to fix (4)? Refer image

Query Used:

    SELECT distinct(lead_email), lead_id, lead_product, struct<lead_reason string> (lead_reason) /*lead_reason*/, lead_date, lead_employee_count, lead_code, 
struct<contact_cancel_date timestamp> (null) contact_cancel_date,/*null as contact_cancel_date*/ /*null as code_uk*/ struct<code_uk string> (null) code_uk FROM `sample_leads`
union all 
select contact_email,contact_id, contact_product, struct<lead_reason string> (null) contact_reason, contact_date, employee,code_us,struct<contact_cancel_date timestamp> (contact_cancel_date),struct<code_uk string> (code_uk) from `sample_contacts`
left join
(
select account_id, employee, code_us, code_uk from `sample_accounts`)
on contact_id=account_id
where `sample_contacts`.contact_email NOT IN (SELECT lead_email FROM `sample_leads`)
    OR `sample_contacts`.contact_product NOT IN (SELECT lead_product FROM `sample_leads`)
   -- group by 1,2,3,5,6,7,8,9
   -- order by lead_id

Note: "id" and "employee" columns are integer, "date" is timestamp, remaining columns are string.

Leads:

lead_id lead_email  lead_product lead_reason lead_date lead_employee_count  lead_code
1   [email protected] msoffice abc  2020-02-23 07:30:02 UTC   1000    1005-C
2   [email protected] chrome pqr1 2020-02-23 07:30:02 UTC   2000    2006-B
3   [email protected]  iphone  xyz  2020-02-23 07:30:02 UTC   3000    3007-A
4   [email protected]  macbook zzz  2020-02-23 07:30:02 UTC   4000    4008-B
5   [email protected] itunes  xyz1 2020-02-23 07:30:02 UTC   5000    5001-A
6   [email protected] googlecloud xyz2 2020-02-23 07:30:02 UTC 6000 6002-B
7   [email protected] yahoomail junk 2020-02-23 07:30:02 UTC  7000    7003-A
8   [email protected]            2020-02-23 07:30:02 UTC   8000    8004-B
2   [email protected]   chrome  pqr1    2020-02-23 07:30:02 UTC 2000    2006-B
5   [email protected]    itunes  xyz1    2020-02-23 07:30:02 UTC 5000    5001-A

Contacts:

    contact_id  contact_email   contact_product contact_date        contact_cancel_date
    9                         msoffice  2010-01-23 07:30:01 UTC 2020-02-23 
 07:30:02 UTC
    10         [email protected]   playstore  2010-01-23 07:30:01 UTC 2020-02-23 07:30:02 UTC
    11         [email protected]              2010-01-23 07:30:01 UTC 2020-02-23 07:30:02 UTC
    12         [email protected]            2010-01-23 07:30:01 UTC 2020-02-23 07:30:02 UTC
    13        [email protected]  ipod    2010-01-23 07:30:01 UTC 2020-02-23 07:30:02 UTC
    14                      googlecloud 2010-01-23 07:30:01 UTC 2020-02-23 07:30:02 UTC
    15        [email protected]           2010-01-23 07:30:01 UTC 2020-02-23 07:30:02 UTC
    16        [email protected]             2010-01-23 07:30:01 UTC 2020-02-23 07:30:02 UTC

Accounts:

account_id  employee    code_us code_uk
9            100        001-A   450-a
10           200        002-B   451-a
11           300        003-A   452-a
12           400        004-B   453-a
13           500        005-C   454-a
14           600        006-B   455-a
15           700        007-A   456-a
16           800        008-B   457-a

enter image description here

1
google-bigquery
Hello @MikhailBerlyant, is it possible for you to help? – NikM
Can you kindly write your tables not as an image? It would help me a lot reproducing your problem – rmesteves
Yes, please simplify the question to its basic - so we can focus on solving the problem that's stopping the whole process from running. – Felipe Hoffa
Hello @rmesteves and FelipeHoffa - I've edited my question and added 3 source tables (lead, contact, account) as text. I've manually tried a lot to make it easy for you to work, by doing lot of formatting, as it's difficult to copy-paste from excel/csv in stackoverflow, in right format. Hope this helps both of you. – NikM

1 Answers

2
votes

Below is for BigQuery Standard SQL 

#standardSQL
SELECT
    leadid,
    lead_employee,
    lead_product,
    lead_reason, 
    lead_date,
    COALESCE(lead_employee_count_code_us, employee) AS lead_employee_count_code_us,
    contact_cancel_date,
    COALESCE(lead_code, code_us) AS lead_code,
    code_uk
FROM (
  SELECT DISTINCT
    lead_id AS leadid,
    lead_email AS lead_employee,
    lead_product,
    lead_reason, 
    lead_date,
    lead_employee_count AS lead_employee_count_code_us,
    CAST(NULL AS TIMESTAMP) AS contact_cancel_date,
    lead_code
  FROM `project.dataset.leads`
    UNION ALL
  SELECT 
    contact_id, 
    contact_email,
    contact_product,
    '',
    contact_date, 
    NULL,
    contact_cancel_date,
    ''
  FROM `project.dataset.contacts`
  WHERE NOT EXISTS (SELECT lead_email FROM `project.dataset.leads` WHERE lead_email = contact_email)
  AND NOT EXISTS (SELECT lead_product FROM `project.dataset.leads` WHERE lead_product = contact_product)
)
LEFT JOIN `project.dataset.accounts`
ON leadid = account_id

If to apply to sample data from your question - result is 

Row leadid  lead_employee       lead_product    lead_reason lead_date               lead_employee_count_code_us contact_cancel_date lead_code   code_uk  
1   1       [email protected]         msoffice        abc         2020-02-23 07:30:02 UTC 1000    null                    1005-C  null     
2   2       [email protected]       chrome          pqr1        2020-02-23 07:30:02 UTC 2000    null                    2006-B  null     
3   3       [email protected]         iphone          xyz         2020-02-23 07:30:02 UTC 3000    null                    3007-A  null     
4   4       [email protected]         macbook         zzz         2020-02-23 07:30:02 UTC 4000    null                    4008-B  null     
5   5       [email protected]        itunes          xyz1        2020-02-23 07:30:02 UTC 5000    null                    5001-A  null     
6   6       [email protected]   googlecloud     xyz2        2020-02-23 07:30:02 UTC 6000    null                    6002-B  null     
7   7       [email protected]         yahoomail       junk        2020-02-23 07:30:02 UTC 7000    null                    7003-A  null     
8   8       [email protected]      null            null        2020-02-23 07:30:02 UTC 8000    null                    8004-B  null     
9   10      [email protected]         playstore                   2010-01-23 07:30:01 UTC 200     2020-02-23 07:30:02 UTC         451-a    
10  12      [email protected]       null                        2010-01-23 07:30:01 UTC 400     2020-02-23 07:30:02 UTC         453-a    
11  13      [email protected]    ipod                        2010-01-23 07:30:01 UTC 500     2020-02-23 07:30:02 UTC         454-a    
12  15      [email protected]     null                        2010-01-23 07:30:01 UTC 700     2020-02-23 07:30:02 UTC         456-a    
13  16      [email protected]       null                        2010-01-23 07:30:01 UTC 800     2020-02-23 07:30:02 UTC         457-a