CODEWITHSUNDEEP
sqlfirebasegoogle-bigqueryfirebase-analytics
user3321348
user3321348

Reputation: 95

BigQuery - Select event param value based on another event param

enter image description hereIn my app, I send an event 'content_view' to Firebase analytics whenever a user opens one a certain view. This event has two event parameters associated with it: content_type and title. content_type is always either 'audio', 'story', 'video', or 'image' and then title is self-explanatory.

That data then goes into Big Query.

In Big Query, I want to list out the top 5 videos with the most views by title and number of events. I can get the top 5 events by title, but I can't figure out how to only do that only for events where content_type = 'video'.

Is there a way to build a query that returns something like this?

+---------+-------------+--------------+
|  Title  | Total Views | Content Type |
+---------+-------------+--------------+
| Video 1 |        1000 | video        |
| Video 2 |         900 | video        |
| Video 3 |         800 | video        |
| Video 4 |         700 | video        |
| Video 5 |         600 | video        |
+---------+-------------+--------------+

Upvotes: 1

Views: 3120

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below is for BigQuery Standard SQL and is based on (and tested for) schema you provided 

#standardSQL
SELECT 
  (
    SELECT MAX(IF(key = 'title', value.string_value, NULL)) title
    FROM UNNEST(event_params) param 
    WHERE (key = 'content_type' AND value.string_value = 'video')
    OR key = 'title'
    HAVING COUNT(1) = 2
  ) Title,
  COUNT(1) TotalViews
FROM `project.dataset.table`
GROUP BY Title
HAVING NOT title IS NULL
ORDER BY TotalViews DESC
LIMIT 5

Upvotes: 3

John Hanley
John Hanley

Reputation: 81356

Modify to suit your column names and the name of your project/dataset/table name:

SELECT title, total_views, content_type
FROM `myproject.mydataset.mytable`
WHERE content_type = 'video'

Upvotes: 0

Related Questions