35 lines
801 B
SQL
35 lines
801 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 ranking,
|
|
transaction_ts,
|
|
symbol,
|
|
price,
|
|
amount,
|
|
dollar_amount,
|
|
type,
|
|
trans_id,
|
|
year,
|
|
month,
|
|
day,
|
|
hour
|
|
FROM "datalake_raw_613161579745_rc_1201682"."crawler_stockdata"
|
|
ORDER BY HourlyBucket
|
|
)
|
|
SELECT
|
|
HourlyBucket,
|
|
ranking,
|
|
transaction_ts,
|
|
symbol,
|
|
price,
|
|
amount,
|
|
dollar_amount,
|
|
type,
|
|
trans_id,
|
|
year,
|
|
month,
|
|
day,
|
|
hour
|
|
FROM cte
|
|
WHERE ranking = 1
|
|
ORDER BY HourlyBucket, symbol, type |