CODEWITHSUNDEEP
sqldatabasegoogle-bigquery
Hutch
Hutch

Reputation: 501

SQL BigQuery - Compare total counts from two tables

I have two tables one and I would like to compare the event counts from each and then get the difference.

I would like to check a given date and see if the EventType from table A matches with the associated column in Table B, so for instance Table A EventType Bounce count 6 and Table B total_bounces 166, then if I can have a result that says there isn't a match or something like +160 in table B etc

I am not sure how I compare a row with a column table, suggestions greatly appreciated.

Table A

Row Table

Table B

Column Table

Upvotes: 0

Views: 1013

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Below is for BigQuery Standard SQL

#standardSQL
SELECT EventDate,
  b.total_sents - a.total_sents AS total_sents_diff,
  b.total_bounces - a.total_bounces AS total_bounces_diff,
  b.total_opens - a.total_opens AS total_opens_diff,
  b.total_clicks - a.total_clicks AS total_clicks_diff
FROM (
  SELECT EventDate,
    SUM(IF(EventType IN ('Bounce', 'Open'), EventCount, 0)) AS total_sents,
    SUM(IF(EventType = 'Bounce', EventCount, 0)) AS total_bounces,
    SUM(IF(EventType = 'Open', EventCount, 0)) AS total_opens,
    SUM(IF(EventType = 'Click', EventCount, 0)) AS total_clicks
  FROM `project.dataset.tableA`
  GROUP BY EventDate
) a
LEFT JOIN `project.dataset.tableB` b
USING(EventDate)

you can test, play with above using sample data from your question as in below example

#standardSQL
WITH `project.dataset.tableA` AS (
  SELECT '2020-07-05' EventDate, 'Bounce' EventType, 6 EventCount UNION ALL
  SELECT '2020-07-05', 'Click', 16737 UNION ALL
  SELECT '2020-07-05', 'Open', 187400 UNION ALL
  SELECT '2020-07-06', 'Bounce', 16 UNION ALL
  SELECT '2020-07-06', 'Click', 26737 UNION ALL
  SELECT '2020-07-06', 'Open', 387400 
), `project.dataset.tableB` AS (           
  SELECT '2020-07-05' EventDate, 155057 total_sents, 166 total_bounces, 75361 total_opens, 8783 total_clicks UNION ALL
  SELECT '2020-07-06', 255057, 266, 85361, 9783 
)
SELECT EventDate,
  b.total_sents - a.total_sents AS total_sents_diff,
  b.total_bounces - a.total_bounces AS total_bounces_diff,
  b.total_opens - a.total_opens AS total_opens_diff,
  b.total_clicks - a.total_clicks AS total_clicks_diff
FROM (
  SELECT EventDate,
    SUM(IF(EventType IN ('Bounce', 'Open'), EventCount, 0)) AS total_sents,
    SUM(IF(EventType = 'Bounce', EventCount, 0)) AS total_bounces,
    SUM(IF(EventType = 'Open', EventCount, 0)) AS total_opens,
    SUM(IF(EventType = 'Click', EventCount, 0)) AS total_clicks
  FROM `project.dataset.tableA`
  GROUP BY EventDate
) a
LEFT JOIN `project.dataset.tableB` b
USING(EventDate)

with output

Row EventDate   total_sents_diff    total_bounces_diff  total_opens_diff    total_clicks_diff    
1   2020-07-05  -32349              160                 -112039             -7954    
2   2020-07-06  -132359             250                 -302039             -16954

Upvotes: 1

Related Questions