-- forked from sssstatistic / Land Activity Points - Season @ https://flipsidecrypto.xyz/sssstatistic/q/E3e5MtpQD7-Q/land-activity-points---season -- land war season time with megawar_times as ( select decoded_log['seasonId'] AS season, to_timestamp(decoded_log['endTime']) + interval '1 hour' AS start_time, CASE WHEN decoded_log['seasonId'] = 3 THEN to_timestamp(decoded_log['endTime']) + interval '8 day' - interval '1 hour' WHEN decoded_log['seasonId'] = 4 THEN to_timestamp(decoded_log['endTime']) + interval '5 day' + interval '10 hour' WHEN decoded_log['seasonId'] = 5 THEN to_timestamp(decoded_log['endTime']) + interval '5 day' + interval '10 hour' WHEN decoded_log['seasonId'] = 6 THEN to_timestamp(decoded_log['endTime']) + interval '5 day' + interval '10 hour' WHEN decoded_log['seasonId'] = 7 THEN to_timestamp(decoded_log['endTime']) + interval '5 day' + interval '10 hour' WHEN decoded_log['seasonId'] = 8 THEN to_timestamp(decoded_log['endTime']) + interval '5 day' + interval '10 hour' WHEN decoded_log['seasonId'] = 9 THEN to_timestamp(decoded_log['endTime']) + interval '5 day' + interval '11 hour' WHEN decoded_log['seasonId'] = 10 THEN to_timestamp(decoded_log['endTime']) + interval '5 day' + interval '11 hour' WHEN decoded_log['seasonId'] = 11 THEN to_timestamp(decoded_log['endTime']) + interval '5 day' + interval '11 hour' WHEN decoded_log['seasonId'] = 12 THEN to_timestamp(decoded_log['endTime']) + interval '5 day' + interval '11 hour' ELSE to_timestamp(decoded_log['endTime']) + interval '5 day' - interval '1 hour' END AS end_time from blast.core.ez_decoded_event_logs where contract_address in ( '0xc48f6213a1a4d3ead79db812c2b91817980532c3' ) and event_name in ( 'NewSeasonStarted' ) and ( {{Season}} = 0 or decoded_log['seasonId'] = {{Season}} + 2 ) ), land AS ( select decoded_log ['account'] AS address, decoded_log ['landId'] AS landId, block_number, block_timestamp from blast.core.ez_decoded_event_logs where contract_address = '0xc48f6213a1a4d3ead79db812c2b91817980532c3' and event_name = 'NewAccount' UNION ALL SELECT decoded_log ['account'] AS address, decoded_log ['toLandId'] AS landId, block_number, block_timestamp from blast.core.ez_decoded_event_logs el where contract_address = '0xc48f6213a1a4d3ead79db812c2b91817980532c3' and event_name in ( 'TeleportToLand', 'KickPlayerOutOfLand' ) ), tables AS ( -- Lucky Coin 500 points per 10 billion Interest select decoded_log ['tableId'] AS tableId, max(decoded_log ['host']) AS host, max(decoded_log ['player']) AS player, max(decoded_log ['winner']) AS winner, max(decoded_log ['cost'] / pow(10, 18)) AS cost, max(decoded_log ['reward'] / pow(10, 18)) AS reward, max(block_number) AS block_number, max(block_timestamp) AS block_timestamp from blast.core.ez_decoded_event_logs el where contract_address = '0xaf466677d96e6df2e31a86fda7e54a31d64647ed' and event_name in ('NewTable', 'Play', 'Claim') GROUP BY decoded_log ['tableId'] -- Lucky Coin 500 points per 10 billion Interest ), info AS ( -- winner SELECT winner as address, sum(cost * 90 / 100) AS reward, max(cost) AS cost, count(*) as times, ld.landId, mt.season FROM tables tb inner join megawar_times mt on tb.block_timestamp BETWEEN mt.start_time and mt.end_time inner join land ld on ld.address = tb.winner and ld.block_number < tb.block_number WHERE winner is not NULL and not exists ( select 1 from land ld2 where ld2.address = ld.address and ld2.block_number > ld.block_number and ld2.block_number < tb.block_number ) group by winner, ld.landId, mt.season UNION ALL -- unlucky SELECT CASE WHEN winner = player then host ELSE player END AS address, sum(cost * -1) AS reward, sum(cost) as cost, count(*) as times, ld.landId, mt.season FROM tables tb inner join megawar_times mt on tb.block_timestamp BETWEEN mt.start_time and mt.end_time inner join land ld on ld.address = CASE WHEN tb.winner = tb.player then tb.host ELSE tb.player END and ld.block_number < tb.block_number WHERE winner is not NULL group by CASE WHEN winner = player then host ELSE player END, ld.landId, mt.season ), -- Lucky Coin 500 points per 10 billion Interest lucky as ( select address AS origin_from_address, round(sum(reward) / pow(10, 10)) * CASE season WHEN 4 THEN 1000 WHEN 5 THEN 1000 WHEN 6 THEN 1000 ELSE 500 END AS value, landId, season FROM info if GROUP by address, landId, season having sum(reward) > 0 ), boss as ( select decoded_log ['bossId'] bossId, ld.landId from blast.core.ez_decoded_event_logs el inner join land ld on ld.address = el.origin_from_address and ld.block_number < el.block_number where contract_address in ( '0xc48f6213a1a4d3ead79db812c2b91817980532c3' ) and event_name in ( 'SummonBoss' ) and not exists ( select 1 from land ld2 where ld2.address = ld.address and ld2.block_number > ld.block_number and ld2.block_number < el.block_number ) ), data as ( -- Skin Shoping 10 points per 1 billion BuyItemFromShop select origin_from_address, round(sum(decoded_log ['cost']) / pow(10, 18) / pow(10, 9)) * (CASE mt.season WHEN 6 THEN 100 WHEN 7 THEN 50 WHEN 8 THEN 50 WHEN 9 THEN 50 WHEN 10 THEN 50 WHEN 11 THEN 50 WHEN 12 THEN 50 ELSE 10 END) AS value, ld.landId, mt.season from blast.core.ez_decoded_event_logs el inner join megawar_times mt on el.block_timestamp BETWEEN mt.start_time and mt.end_time left join land ld on ld.address = el.origin_from_address and ld.block_number < el.block_number where contract_address in ( '0xc48f6213a1a4d3ead79db812c2b91817980532c3' ) and event_name in ('BuyItemFromShop') and decoded_log ['itemType'] in (1, 2) and not exists ( select 1 from land ld2 where ld2.address = ld.address and ld2.block_number > ld.block_number and ld2.block_number < el.block_number ) group by el.origin_from_address, ld.landId, mt.season UNION all /** Last Samurai 5 points per play Boss Assist 10 points per assist AttackBoss Boss Hunter 500 points per successful summon SummonBoss Level up 5 points each time LevelUpSamurai + LevelUpPet **/ select origin_from_address, CASE mt.season WHEN 4 THEN 50 WHEN 5 THEN 50 WHEN 6 THEN 100 WHEN 7 THEN 50 WHEN 8 THEN 50 WHEN 9 THEN 50 WHEN 10 THEN 50 WHEN 11 THEN 50 WHEN 12 THEN 50 ELSE 10 END AS value, ld.landId, mt.season from blast.core.ez_decoded_event_logs el inner join megawar_times mt on el.block_timestamp BETWEEN mt.start_time and mt.end_time inner join land ld on ld.address = el.origin_from_address and ld.block_number < el.block_number where contract_address in ( '0xc48f6213a1a4d3ead79db812c2b91817980532c3' ) and event_name in ( 'AttackBoss' ) and not exists ( select 1 from land ld2 where ld2.address = ld.address and ld2.block_number > ld.block_number and ld2.block_number < el.block_number ) and exists ( select 1 from boss bo WHERE bo.bossId = el.decoded_log ['bossId'] AND bo.landId = ld.landId ) UNION all select origin_from_address, case event_name when 'SummonBoss' then ( CASE mt.season WHEN 6 THEN 1000 WHEN 7 THEN 1000 WHEN 8 THEN 1000 WHEN 9 THEN 1000 WHEN 10 THEN 1000 WHEN 11 THEN 2000 WHEN 12 THEN 2000 ELSE 500 END ) when 'LevelUpSamurai' then ( CASE mt.season WHEN 6 THEN 10 WHEN 8 THEN 10 WHEN 9 THEN 30 WHEN 10 THEN 30 WHEN 11 THEN 30 WHEN 12 THEN 30 ELSE 5 END ) when 'LevelUpPet' then ( CASE mt.season WHEN 6 THEN 10 WHEN 8 THEN 10 WHEN 9 THEN 30 WHEN 10 THEN 30 WHEN 11 THEN 30 WHEN 12 THEN 30 ELSE 5 END ) when 'Deposit' then ( CASE mt.season WHEN 4 THEN 3 WHEN 5 THEN 2 WHEN 6 THEN 2 WHEN 7 THEN 2 WHEN 8 THEN 2 ELSE 5 END ) else 0 end as value, ld.landId, mt.season from blast.core.ez_decoded_event_logs el inner join megawar_times mt on el.block_timestamp BETWEEN mt.start_time and mt.end_time inner join land ld on ld.address = el.origin_from_address and ld.block_number < el.block_number where contract_address in ( '0xc48f6213a1a4d3ead79db812c2b91817980532c3', '0x74d9c915c64ab7587a3abde1827f3a09eac97bac' ) and event_name in ( 'SummonBoss', 'LevelUpSamurai', 'LevelUpPet', 'Deposit' ) and not exists ( select 1 from land ld2 where ld2.address = ld.address and ld2.block_number > ld.block_number and ld2.block_number < el.block_number ) UNION all -- Renewal 10 points * per 20M rice bag WithdrawRewardFromPool select origin_from_address, decoded_log ['points'] / pow(10, 18) / (2 * pow(10, 7)) * CASE mt.season WHEN 4 THEN 20 WHEN 5 THEN 20 WHEN 6 THEN 100 WHEN 7 THEN 30 WHEN 8 THEN 30 WHEN 9 THEN 30 WHEN 10 THEN 30 WHEN 11 THEN 30 WHEN 12 THEN 30 ELSE 10 END AS value, ld.landId, mt.season from blast.core.ez_decoded_event_logs el inner join megawar_times mt on el.block_timestamp BETWEEN mt.start_time and mt.end_time left join land ld on ld.address = el.origin_from_address and ld.block_number < el.block_number where contract_address in ( '0xc48f6213a1a4d3ead79db812c2b91817980532c3' ) and event_name in ('WithdrawRewardFromPool') and not exists ( select 1 from land ld2 where ld2.address = ld.address and ld2.block_number > ld.block_number and ld2.block_number < el.block_number ) UNION all select origin_from_address, case when value > 0 then value else 0 end as value, landId, season from lucky ) select CASE WHEN season <= 2 THEN concat('Preparation Season: ', season) ELSE concat('Season: ', season - 2) END AS "Season", concat('#', landId) AS "Land ID", round(sum(value)) as points from data group by CASE WHEN season <= 2 THEN concat('Preparation Season: ', season) ELSE concat('Season: ', season - 2) END, concat('#', landId) order by "Season" DESC, points desc;

Query	Creator	Created
Lucky Coin: Blast Gold Event (11th Oct 3 AM UTC - 28th Oct 3 AM UTC) copy blast l2 evm	wereez-5PLQDz	Oct 28, 2024
Referral I (2024-10-11 03AM - 2024-10-18 03AM UTC) copy blast l2 evm	wereez-5PLQDz	Oct 18, 2024
Land Activity Points - Season copy blast l2 evm	wereez-5PLQDz	Sep 16, 2024

wereez-5PLQDz