CODEWITHSUNDEEP
sqlfirebasegoogle-bigqueryfirebase-analytics
skorada
skorada

Reputation: 461

How to run a BigQuery query that can collate unique data between 2 events

I am trying to query the database to fetch data about users who have completed 2 actions.

For example:

event_1 = "clicked_on_feedback" : event_params.key = "rating"
event_2 = "burger_chosen"       : event_params.key = "burger_type"

Now I am trying to see the users who have given negative feedback what type of burgers did they choose to order? How do I do this for different events which have different param_keys?

I was able to fetch the list of users who have given negative rating

SELECT user_id,event_name, param.value.string_value AS rating FROM `table.events_*`,
UNNEST(event_params) AS param
where event_name IN ("clicked_on_feedback")
AND event_timestamp >
      UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 10 DAY))
AND param.key = "rating"

Result

user_id  event_name          rating
abcxxx   clicked_on_feedback 1
abcdxx   clicked_on_feedback 2

How do I for these set of users get the type of burger they chose to order in the same interval?

Expected:

user_id    event_name_1          rating   event_name_2       burger_type
abcxxx     clicked_on_feedback   1        burger_chosen      triple_decker
abcdxx     clicked_on_feedback   2        burger_chosen      cheesy_chicken

Upvotes: 0

Views: 697

Answers (1)

Parfait
Parfait

Reputation: 107652

Simply join two SELECT statements which you can define as two CTEs: one for rating and other for burger_chosen. Below is an untested SQL query, requiring needed adjustment on table and fields.

WITH rate AS
 (SELECT user_id, event_name, param.value.string_value AS rating 
  FROM `table.events_*`, UNNEST(event_params) AS param
  WHERE event_name IN ("clicked_on_feedback")
    AND event_timestamp > UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 10 DAY))
    AND param.key = "rating"
 ), brgr AS 
 (SELECT user_id, event_name, param.value.string_value AS burger_type
  FROM `table.events_*`, UNNEST(event_params) AS param
  WHERE event_name IN ("burger_chosen")
    AND event_timestamp > UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 10 DAY))
    AND param.key = "burger_type")

SELECT rate.user_id, rate.event_name AS event_name_1, rate.rating,
       brgr.event_name AS event_name_2, brgr.burger_type
FROM rate  
INNER JOIN brgr ON rate.user_id = brgr.user_id

Upvotes: 1

Related Questions