SQL Query - HackMD

Table (in Excel Sheet format)

FruitName	Market	weight	size	timestamp
Apple	East	100	M	100
Banana	West	200	L	100

sheet_version	timestamp
v1	100

id	FruitName	Market
1	Apple	East
2	Banana	West

id	key_id	weight	size	timestamp
10	1	100	M	100
11	2	200	L	100

id	FruitName	Market	id	key_id	weight	size	timestamp
1	Apple	East	10	1	100	M	100
2	Banana	West	11	2	200	L	100

sheet_version	timestamp
v1	100
v2	200

id	key_id	weight	size	timestamp
10	1	100	M	100
11	1	100	L	200
12	2	200	L	100

id	FruitName	Market	id	key_id	weight	size	timestamp
1	Apple	East	12	1	100	L	200
2	Banana	West	11	2	200	L	100

Original Query

SELECT
 dbKey.*, finalDBData.*
FROM
    dbKey,
    (
        SELECT
        *,
        rank() OVER (PARTITION BY key_id ORDER BY TIMESTAMP DESC) AS rank
        FROM
        dbData where "timestamp" <= 101
    ) finalDBData
where
rank =1 
and finalDBData.key_id = dbKey.id;

Refactored Query

v2_map:

key_id	timestamp
1	100
2	200

WITH map AS (
  SELECT 
    DISTINCT(key_id),
    timestamp
  FROM (
    SELECT
     key_id,
     timestamp,
     rank() OVER (PARTITION BY key_id ORDER BY TIMESTAMP DESC) AS rank
    FROM
    dbData
    -- filtering stuff depends on business logic
    where key_id in (1,2)
  ) sub WHERE rank = 1
) 
SELECT
 dbKey.*, dbData.*
FROM
 dbKey
 INNER JOIN map m ON m.key_id = dbKey.id
 INNER JOIN dbData ON dbData.key_id = m.key_id AND m.timestamp = dbData.timestamp;

SQL Query Performance Optimization

Table (in Excel Sheet format)

version 1

version 2