construct a QUERY in BigQuery
Budget: $250 – $750 USD
We have two tables (sample csv attached):
1. user_event table which contains the user_id (uuid) and item_id and timestamp
2. item table which contains the item_id (id) and tag array (array)
We'd like to construct a query and schedule it in BigQuery to do the following job:
For a period of time,
-(for example 10 days event history: where timestamp BETWEEN TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -10 DAY) AND CURRENT_TIMESTAMP() )
-(event_type = 4 add to favorite)
Get the top 20 tags for each user "add to favorite" based on the item_id in the event (if a user does not have any event history of "add to favorite" in the period of time, then we ignore the user. I believe we should already filter out these users by using the where clause mentioned above.
and save the result to a new table (let's say user_fav, insert a new row if uuid doesn't exist, update/merge the old row if uuid exists in the table already)
Hope the description is clear.
1. user_event table which contains the user_id (uuid) and item_id and timestamp
2. item table which contains the item_id (id) and tag array (array)
We'd like to construct a query and schedule it in BigQuery to do the following job:
For a period of time,
-(for example 10 days event history: where timestamp BETWEEN TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -10 DAY) AND CURRENT_TIMESTAMP() )
-(event_type = 4 add to favorite)
Get the top 20 tags for each user "add to favorite" based on the item_id in the event (if a user does not have any event history of "add to favorite" in the period of time, then we ignore the user. I believe we should already filter out these users by using the where clause mentioned above.
and save the result to a new table (let's say user_fav, insert a new row if uuid doesn't exist, update/merge the old row if uuid exists in the table already)
Hope the description is clear.