What Is The Most Efficient Query To Get Latest Rows With Group By Clause On Joined Tables In Bigquery?

Current Design Table: 1_notes ------------------------------------------ | id | text | created_at | ------------------------------------------ | 1_1 | u1 first

Solution 1:

Below is for BigQuery Standard SQL

#standardSQL
WITH `project.dataset.user_notes` AS (
  SELECT*FROM `project.dataset.user1_notes` UNIONALLSELECT*FROM `project.dataset.user2_notes`
), `project.dataset.user_note_timeline` AS (
  SELECT*FROM `project.dataset.user1_note_timeline` UNIONALLSELECT*FROM `project.dataset.user2_note_timeline`
)
SELECT note_id, note_created_at, likes_count, text 
FROM (
  SELECT note_id, ARRAY_AGG(STRUCT(note_created_at, likes_count, created_at) ORDERBY created_at DESC LIMIT 1)[OFFSET(0)].*FROM `project.dataset.user_note_timeline`
  GROUPBY note_id
  ORDERBY likes_count DESC, note_created_at
  LIMIT 2
) t
JOIN `project.dataset.user_notes` n
ON note_id  = id

Solution 2:

Hope this will solve your issue

SELECT TOP 1 t1.note_id, t1.note_created_at, t1.likes_count, t2.[text]
  FROM1_note_timeline t1 INNER JOIN1_notes t2 ON t1.note_id = t2.Id
 ORDERBY t1.created_at DESC

UNION

SELECT TOP 1 t1.note_id, t1.note_created_at, t1.likes_count, t2.[text]
  FROM2_note_timeline t1 INNER JOIN2_notes t2 ON t1.note_id = t2.Id
 ORDERBY t1.created_at DESC

It is better to introduce indices for the tables in order to improve performance.