How To Obtain The Most Recent Row Per Type And Perform Calculations, Depending On The Row Type?

I need some help writing/optimizing a query to retrieve the latest version of each row by type and performing some calculations depending on the type. I think would be best if I il

Solution 1:

How can this query be optimized?

Try below version

#standardSQL
WITH types AS (
  SELECT 
    FORMAT_TIMESTAMP('%Y-%m-%d', sent_at) AS sent_at,
    message_id,
    FIRST_VALUE(status) OVER(PARTITIONBY message_id ORDERBY (event_type = "create") DESC, event_timestamp DESC) AS submitted_status,
    FIRST_VALUE(status) OVER(PARTITIONBY message_id ORDERBY (event_type = "status_update") DESC, event_timestamp DESC) AS delivered_status,
    FIRST_VALUE(rate) OVER(PARTITIONBY message_id ORDERBY (event_type IN ("rate_update", "create")) DESC, event_timestamp DESC) AS sales_rate
  FROM events
), latest AS (
  SELECT 
    sent_at,
    message_id,
    ANY_VALUE(IF(submitted_status=0,1,0)) AS submitted,  
    ANY_VALUE(IF(delivered_status=1,1,0)) AS delivered,  
    ANY_VALUE(sales_rate) AS sales_rate
  FROM types
  GROUPBY1, 2
)
SELECT   
  sent_at,
  SUM(submitted) AS submitted,  
  SUM(delivered) AS delivered,  
  SUM(sales_rate) AS sales_rate_total        
FROM latest
GROUPBY1

It's compact enough to easily manage, no redundancy, no joins at all, etc. If your table partitioned - you can easily use it by adjusting query just in one place

You can use below dummy data if want to check above query on low volume first

WITH events AS (
  SELECT1AS id, 'create'AS event_type, TIMESTAMP'2016-11-25 09:17:48'AS event_timestamp, 1AS message_id, TIMESTAMP'2016-11-25 09:17:48'AS sent_at, 0AS status, 0.500000AS rate UNIONALLSELECT2AS id, 'status_update'AS event_type, TIMESTAMP'2016-11-25 09:24:38'AS event_timestamp, 1AS message_id, TIMESTAMP'2016-11-25 09:28:49'AS sent_at, 1AS status, 0.500000AS rate UNIONALLSELECT3AS id, 'create'AS event_type, TIMESTAMP'2016-11-25 09:47:48'AS event_timestamp, 2AS message_id, TIMESTAMP'2016-11-25 09:47:48'AS sent_at, 0AS status, 0.500000AS rate UNIONALLSELECT4AS id, 'status_update'AS event_type, TIMESTAMP'2016-11-25 09:54:38'AS event_timestamp, 2AS message_id, TIMESTAMP'2016-11-25 09:48:49'AS sent_at, 1AS status, 0.500000AS rate UNIONALLSELECT5AS id, 'rate_update'AS event_type, TIMESTAMP'2016-11-25 09:55:07'AS event_timestamp, 2AS message_id, TIMESTAMP'2016-11-25 09:50:07'AS sent_at, 0AS status, 1.000000AS rate UNIONALLSELECT6AS id, 'create'AS event_type, TIMESTAMP'2016-11-26 09:17:48'AS event_timestamp, 3AS message_id, TIMESTAMP'2016-11-26 09:17:48'AS sent_at, 0AS status, 0.500000AS rate UNIONALLSELECT7AS id, 'create'AS event_type, TIMESTAMP'2016-11-27 09:17:48'AS event_timestamp, 4AS message_id, TIMESTAMP'2016-11-27 09:17:48'AS sent_at, 0AS status, 0.500000AS rate UNIONALLSELECT8AS id, 'rate_update'AS event_type, TIMESTAMP'2016-11-27 09:55:07'AS event_timestamp, 4AS message_id, TIMESTAMP'2016-11-27 09:50:07'AS sent_at, 0AS status, 2.000000AS rate UNIONALLSELECT9AS id, 'rate_update'AS event_type, TIMESTAMP'2016-11-27 09:55:07'AS event_timestamp, 2AS message_id, TIMESTAMP'2016-11-25 09:55:07'AS sent_at, 0AS status, 2.000000AS rate 
)

Solution 2:

For every table that holds multiple events and where we need to pick the latest we have a view in place.

View: user_profile_latest

SELECT*from (
  selectrank() over (partitionby user_id orderby bq.created DESC, bq.insert_id  desc) as _rank,
*FROM [user_profile_event]
) where _rank=1

We maintain a record BQ with created and insert_id for deduplication purposes.