12 lines
618 B
SQL
12 lines
618 B
SQL
WITH cte AS(
|
|
SELECT DISTINCT date_format(from_unixtime(transaction_ts),'%Y-%m-%dT%H') AS HourlyBucket,
|
|
RANK() OVER(PARTITION BY (date_format(from_unixtime(transaction_ts),'%Y-%m-%dT%H')), symbol, type ORDER BY dollar_amount DESC) AS rnk,
|
|
transaction_ts, symbol, price, amount, dollar_amount, type, trans_id, year, month, day, hour
|
|
FROM "datalake_raw_534534002841_ab_1201680"."crawler_stockdata"
|
|
ORDER BY HourlyBucket
|
|
)
|
|
|
|
SELECT HourlyBucket, rnk, transaction_ts, symbol, price, amount, dollar_amount, type, trans_id, year, month, day, hour
|
|
FROM cte
|
|
WHERE rnk = 1
|
|
ORDER BY HourlyBucket, symbol, type |