35 lines
801 B
MySQL
35 lines
801 B
MySQL
![]() |
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
|