15 lines
616 B
SQL
15 lines
616 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_878695318857_ek_1201695"."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; |