Como obter a linha mais recente por tipo e realizar cálculos, dependendo do tipo de linha?

Postei a mesma pergunta no SO, mas pensei em tentar aqui também, já que qualquer outro tipo de otimização e conselho são muito bem-vindos :) De qualquer forma, aqui está minha postagem, palavra por palavra:

Preciso de ajuda para escrever/otimizar uma consulta para recuperar a versão mais recente de cada linha por tipo e realizar alguns cálculos dependendo do tipo. Acho que seria melhor se eu ilustrasse com um exemplo.

Dado o seguinte conjunto de dados:

+-------+-------------------+---------------------+-------------+---------------------+--------+----------+
| id    | event_type        | event_timestamp     | message_id  | sent_at             | status | rate     |
+-------+-------------------+---------------------+-------------+---------------------+--------+----------+
| 1     | create            | 2016-11-25 09:17:48 | 1           | 2016-11-25 09:17:48 | 0      | 0.500000 |
| 2     | status_update     | 2016-11-25 09:24:38 | 1           | 2016-11-25 09:28:49 | 1      | 0.500000 |
| 3     | create            | 2016-11-25 09:47:48 | 2           | 2016-11-25 09:47:48 | 0      | 0.500000 |
| 4     | status_update     | 2016-11-25 09:54:38 | 2           | 2016-11-25 09:48:49 | 1      | 0.500000 |
| 5     | rate_update       | 2016-11-25 09:55:07 | 2           | 2016-11-25 09:50:07 | 0      | 1.000000 |
| 6     | create            | 2016-11-26 09:17:48 | 3           | 2016-11-26 09:17:48 | 0      | 0.500000 |
| 7     | create            | 2016-11-27 09:17:48 | 4           | 2016-11-27 09:17:48 | 0      | 0.500000 |
| 8     | rate_update       | 2016-11-27 09:55:07 | 4           | 2016-11-27 09:50:07 | 0      | 2.000000 |
| 9     | rate_update       | 2016-11-27 09:55:07 | 2           | 2016-11-25 09:55:07 | 0      | 2.000000 |
+-------+-------------------+---------------------+-------------+---------------------+--------+----------+

O resultado esperado deve ser:

+------------+--------------------+--------------------+-----------------------+
| sent_at    | sum(submitted_msg) | sum(delivered_msg) | sum(rate_total)       |
+------------+--------------------+--------------------+-----------------------+
| 2016-11-25 |                  2 |                  2 |              2.500000 |
| 2016-11-26 |                  1 |                  0 |              0.500000 |
| 2016-11-27 |                  1 |                  0 |              2.000000 |
+------------+--------------------+--------------------+-----------------------+

No final do post está a consulta que é utilizada para obter este resultado. Estou disposto a apostar que deve haver uma maneira de otimizá-lo, já que está usando subconsultas com junções e, pelo que li sobre o BigQuery, é melhor evitar junções. Mas primeiro alguns antecedentes:

Em essência, o conjunto de dados representa uma tabela anexada, na qual vários eventos são gravados. O tamanho dos dados está na casa das centenas de milhões e crescerá para mais de bilhões. Como as atualizações no BigQuery não são práticas e os dados estão sendo transmitidos para o BQ, preciso de uma maneira de recuperar o mais recente de cada evento, realizar alguns cálculos com base em determinadas condições e retornar um resultado preciso. A consulta é gerada dinamicamente, com base na entrada do usuário, portanto, mais campos/cálculos podem ser incluídos, mas foram omitidos para simplificar.

• Há apenas um createevento, mas nde qualquer outro tipo
• Para cada grupo de eventos, apenas o último deve ser levado em consideração ao fazer os cálculos.
  • status_update - atualiza o status
  • rate_update - atualiza a taxa
  • criar - auto explicativo
• Todo evento que não é createpode não carregar o restante das informações do original/pode não ser preciso (exceto para message_id e o campo em que o evento está operando) (o conjunto de dados é simplificado, mas imagine que há muito mais colunas e mais eventos serão adicionados mais tarde)
  • Por exemplo, a rate_updatepode ou não ter o campo de status definido, ou ser um valor que não é o final, então nenhum cálculo pode ser feito no campo de status de um rate_updateevento e o mesmo vale parastatus_update
• Pode-se supor que a tabela seja particionada por data e cada consulta fará uso das partições. Essas condições foram omitidas em favor da simplicidade por enquanto.

Então acho que tenho algumas perguntas:

• Como essa consulta pode ser otimizada?
• Será uma ideia melhor colocar os eventos, além de createsuas próprias tabelas, onde os únicos campos disponíveis serão os relevantes para os eventos e necessários para as junções (message_id, event_timestamp)? Isso reduzirá a quantidade de dados processados?
• Qual seria a maneira ideal de adicionar mais eventos no futuro, que terão suas próprias condições e cálculos?

Na verdade, qualquer conselho sobre como consultar esse conjunto de dados de maneira eficiente e amigável é mais do que bem-vindo! Obrigada! :)

A monstruosidade que eu inventei é a seguinte. São INNER JOINSusados ​​para recuperar a versão mais recente de cada linha, conforme este recurso

    select
    sent_at as sent_at,
    sum(submitted_msg) as submitted,
    sum(delivered_msg) as delivered,
    sum(sales_rate_total) as sales_rate_total
    FROM (

      #DELIVERED
        SELECT 
            d.message_id,
            FORMAT_TIMESTAMP('%Y-%m-%d 00:00:00', sent_at) AS sent_at,
            0 as submitted_msg,
            sum(if(status=1,1,0)) as delivered_msg,
            0 as sales_rate_total
        FROM `events` d
        INNER JOIN
                (
                    select message_id, max(event_timestamp) as ts 
                    from `events` 
                    where event_type = "status_update" 
                    group by 1
                    ) g on d.message_id = g.message_id and d.event_timestamp = g.ts
        GROUP BY 1,2

        UNION ALL

      #SALES RATE
        SELECT 
            s.message_id,
            FORMAT_TIMESTAMP('%Y-%m-%d 00:00:00', sent_at) AS sent_at,
            0 as submitted_msg,
            0 as delivered_msg,
            sum(sales_rate) as sales_rate_total
        FROM `events` s
        INNER JOIN 
                    (
                    select message_id, max(event_timestamp) as ts 
                    from `events` 
                    where event_type in ("rate_update", "create")  
                    group by 1
                    ) f on s.message_id = f.message_id and s.event_timestamp = f.ts
        GROUP BY 1,2

        UNION ALL

      #SUBMITTED & REST
        SELECT 
            r.message_id,
            FORMAT_TIMESTAMP('%Y-%m-%d 00:00:00', sent_at) AS sent_at,
            sum(if(status=0,1,0)) as submitted_msg,
            0 as delivered_msg,
            0 as sales_rate_total
        FROM `events` r
        INNER JOIN
                (
                    select message_id, max(event_timestamp) as ts 
                    from `events` 
                    where event_type = "create" 
                    group by 1
                    ) e on r.message_id = e.message_id and r.event_timestamp = e.ts
        GROUP BY 1, 2

    ) k
    group by 1