Skip to content

Instantly share code, notes, and snippets.

@cincauhangus

cincauhangus/allocation.sql

Last active February 26, 2024 09:04
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save cincauhangus/5c65758e0021ad428c781428758e9ffb to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save cincauhangus/5c65758e0021ad428c781428758e9ffb to your computer and use it in GitHub Desktop.
Download ZIP
$DEGEN Airdrop 2 Tip Allocations & Leaderboard Dune Queries
Raw
readme.md
Raw
allocation.sql
WITH date_series AS (
SELECT
DATE_TRUNC( 'day', CAST(day AS DATE) ) AS event_day
FROM
UNNEST(
sequence(
CAST('2021-09-23' AS DATE),
CURRENT_DATE,
INTERVAL '1' DAY
)
) AS t(day)
),
profile AS (
SELECT
profile.fid,
profile.fname,
profile.display_name,
profile.avatar_url,
ROW_NUMBER() OVER (PARTITION BY profile.fid ORDER BY LENGTH(profile.verified_addresses) DESC NULLS LAST) AS rn
FROM dune.neynar.dataset_farcaster_profile_with_addresses profile
),
display_names AS (
SELECT
fid,
value AS display_name,
timestamp,
ROW_NUMBER() OVER (PARTITION BY fid ORDER BY timestamp DESC) AS rn
FROM
dune.neynar.dataset_farcaster_user_data
WHERE
type = 2
),
users AS (
SELECT
fid,
MIN( DATE_TRUNC('day', timestamp) ) AS first_cast_timestamp
FROM
dune.neynar.dataset_farcaster_casts
WHERE
deleted_at IS NULL
AND parent_hash IS NULL
GROUP BY
fid
HAVING
COUNT(*) >= 3
),
daily_users AS (
SELECT
d.event_day,
u.fid,
u.first_cast_timestamp
FROM
date_series d
CROSS JOIN
users u
WHERE
d.event_day >= u.first_cast_timestamp
),
reactions AS (
SELECT
DATE_TRUNC('day', rxn.timestamp) AS event_day,
rxn.target_fid AS fid,
COUNT(DISTINCT rxn.fid) AS reaction_count
FROM
dune.neynar.dataset_farcaster_casts casts
INNER JOIN
dune.neynar.dataset_farcaster_reactions rxn
ON rxn.target_hash = casts.hash
AND rxn.target_fid = casts.fid
WHERE
casts.deleted_at IS NULL
AND casts.parent_hash IS NULL
AND rxn.deleted_at IS NULL
GROUP BY
1,
rxn.target_fid
),
original_casts AS(
SELECT
DATE_TRUNC('day', timestamp) AS event_day,
fid,
COUNT(*) AS cast_count
FROM
dune.neynar.dataset_farcaster_casts
WHERE
deleted_at IS NULL
AND parent_hash IS NULL
GROUP BY
1,
fid
),
daily_stats AS (
SELECT
u.event_day,
u.fid,
u.first_cast_timestamp,
SUM( COALESCE(og.cast_count, 0) ) OVER (
PARTITION BY u.fid
ORDER BY u.event_day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cast_count,
SUM( COALESCE(rxn.reaction_count, 0) ) OVER (
PARTITION BY u.fid
ORDER BY u.event_day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS reaction_count,
APPROX_PERCENTILE( COALESCE(rxn.reaction_count, 1), 0.6 ) OVER (
PARTITION BY u.fid
ORDER BY u.event_day ROWS BETWEEN 7 PRECEDING AND CURRENT ROW
) AS median_reactions_60,
APPROX_PERCENTILE( COALESCE(rxn.reaction_count, 1), 0.5 ) OVER (
PARTITION BY u.fid
ORDER BY u.event_day ROWS BETWEEN 7 PRECEDING AND CURRENT ROW
) AS median_reactions_50,
APPROX_PERCENTILE( COALESCE(rxn.reaction_count, 1), 0.4 ) OVER (
PARTITION BY u.fid
ORDER BY u.event_day ROWS BETWEEN 7 PRECEDING AND CURRENT ROW
) AS median_reactions_40,
APPROX_PERCENTILE( COALESCE(rxn.reaction_count, 1), 0.3 ) OVER (
PARTITION BY u.fid
ORDER BY u.event_day ROWS BETWEEN 7 PRECEDING AND CURRENT ROW
) AS median_reactions_30,
APPROX_PERCENTILE( COALESCE(rxn.reaction_count, 1), 0.2 ) OVER (
PARTITION BY u.fid
ORDER BY u.event_day ROWS BETWEEN 7 PRECEDING AND CURRENT ROW
) AS median_reactions_20
FROM
daily_users u
LEFT JOIN
reactions rxn
ON rxn.event_day = u.event_day
AND rxn.fid = u.fid
LEFT JOIN
original_casts og
ON og.event_day = u.event_day
AND og.fid = u.fid
),
final_stats AS (
SELECT
event_day,
fid,
first_cast_timestamp,
CASE
WHEN cast_count >= 1000 THEN median_reactions_60
WHEN cast_count >= 500 THEN median_reactions_50
WHEN cast_count >= 100 THEN median_reactions_40
WHEN cast_count >= 50 THEN median_reactions_30
ELSE median_reactions_20
END AS median_reactions,
reaction_count / cast_count AS reactions_per_cast
FROM
daily_stats
WHERE
event_day >= CAST('2024-01-27' AS DATE)
AND event_day < CURRENT_DATE
),
allowance AS (
SELECT
stats.event_day,
stats.fid,
stats.first_cast_timestamp,
stats.median_reactions,
stats.reactions_per_cast,
COALESCE(ovrrd.address, verified_eth_addresses.address) AS wallet_address,
profile.fname,
profile.display_name,
profile.avatar_url,
ROUND(LOG10( date_diff('day', stats.first_cast_timestamp, stats.event_day) + 10 ), 0) AS retroactive_boost,
ROUND(
EXP(LOG( 3.3, stats.median_reactions * 1000))
* ROUND(LOG10( date_diff('day', stats.first_cast_timestamp, stats.event_day) + 10 ), 0)
, 0) AS tip_allowance
FROM
final_stats stats
LEFT JOIN
profile
ON profile.fid = stats.fid
AND profile.rn = 1
LEFT JOIN
query_3464901 verified_eth_addresses
ON verified_eth_addresses.fid = stats.fid
LEFT JOIN
query_3423915 ovrrd
ON ovrrd.fid = stats.fid
WHERE
verified_eth_addresses.address IS NOT NULL
AND profile.display_name IS NOT NULL
AND profile.avatar_url IS NOT NULL
)
SELECT
ROW_NUMBER() OVER (
PARTITION BY a.event_day
ORDER BY a.tip_allowance DESC, a.first_cast_timestamp
) AS user_rank,
a.event_day + INTERVAL '1' DAY AS snapshot_date,
a.fid,
CASE
-- Perl
WHEN a.fid = 1110 AND a.tip_allowance <= 375000
THEN 375000
-- EthXY
WHEN a.fid IN (21187, 12309) AND a.tip_allowance <= 75000
THEN 75000
-- Tighten tip allowance printing starting February 22.
WHEN a.event_day + INTERVAL '1' DAY = TO_DATE('20240222', 'yyyymmdd')
THEN a.tip_allowance * 0.70
-- Valentine's Day Giveaway
WHEN a.event_day + INTERVAL '1' DAY = TO_DATE('20240214', 'yyyymmdd')
AND valentines.fid IS NOT NULL
AND dis.display_name LIKE '%🎩%'
THEN a.tip_allowance + 1000000
ELSE a.tip_allowance
END AS tip_allowance,
a.median_reactions,
a.reactions_per_cast,
a.retroactive_boost,
a.wallet_address,
a.fname,
dis.display_name,
a.avatar_url,
SUM(a.tip_allowance * 0.70) OVER (PARTITION BY a.event_day) AS total_degen_per_day
FROM
allowance a
LEFT JOIN
display_names dis
ON dis.fid = a.fid
AND dis.rn = 1
LEFT JOIN
dune.degentokenbase.dataset_airdrop_2_tip_allowances_snapshot_20240214_filtered valentines
ON valentines.fid = a.fid
LEFT JOIN
query_3444616 disqualified
ON disqualified.fid = a.fid
WHERE
a.tip_allowance <> 0
AND disqualified.fid IS NULL
ORDER BY
a.event_day DESC,
4 DESC
;
Raw
leaderboard.sql
WITH profile AS (
SELECT
profile.fid,
profile.fname,
profile.display_name,
profile.avatar_url,
ROW_NUMBER() OVER (PARTITION BY profile.fid ORDER BY LENGTH(profile.verified_addresses) DESC NULLS LAST) AS rn
FROM dune.neynar.dataset_farcaster_profile_with_addresses profile
),
tips AS (
SELECT
-- Tip allowances are loaded at 7:35 UTC. Casts after midnight until then
-- are made against the previous day's tip allowance.
DATE_TRUNC('day', casts.timestamp - INTERVAL '7' HOUR - INTERVAL '35' MINUTE) AS event_day,
casts.parent_fid AS recipient_fid,
casts.fid AS donor_fid,
CAST(REGEXP_EXTRACT(REGEXP_EXTRACT(UPPER(casts.text), '([0-9]+) \$DEGEN'), '[0-9]+') AS DOUBLE) AS tip_amount,
CASE
WHEN root_parent_url IN (
'chain://eip155:8453/erc721:0x1e5115dc60cdab3c1263a945201cb509ea7a8340', -- FarcastHER
'chain://eip155:1/erc721:0x2A9EA02E4c2dcd56Ba20628Fe1bd46bAe2C62746', -- FarCon
'https://warpcast.com/~/channel/frames', --Fframes
'https://onchainsummer.xyz', -- Base
'chain://eip155:1/erc721:0x7dd4e31f1530ac682c8ea4d8016e95773e08d8b0', -- Dev
'chain://eip155:7777777/erc721:0x22be981fb87effbe6780b34a6fe1dfc14a00ec8e', -- Design
'chain://eip155:7777777/erc721:0x3d037b11c5359fac54c3928dfad0b9512695d392', -- Frontend
'https://farcaster.group/founders', -- Founders
'https://warpcast.com/~/channel/perl', -- Perl
'https://farcaster.group/product', -- Product
'chain://eip155:1/erc721:0xca21d4228cdcc68d4e23807e5e370c07577dd152' -- Zora
) THEN 1.5
ELSE 1.0
END AS channel_boost
FROM
dune.neynar.dataset_farcaster_casts casts
WHERE
casts.fid NOT IN (217745, 234434, 244128) -- Exclude @degenbot, @degentip, @degentokenbase
AND REGEXP_LIKE(UPPER(casts.text), '[0-9]+ \$DEGEN')
AND casts.parent_hash IS NOT NULL
AND casts.deleted_at IS NULL
AND casts.timestamp <= CURRENT_DATE
AND casts.parent_fid <> casts.fid
),
daily_donated_tips AS (
SELECT
tips.event_day,
tips.recipient_fid,
tips.donor_fid,
tips.tip_amount,
tips.channel_boost,
SUM(tips.tip_amount) OVER (
PARTITION BY tips.event_day, tips.donor_fid
ORDER BY tips.tip_amount DESC, tips.recipient_fid
) AS donated_amount,
allowance.tip_allowance AS donor_tip_allowance
FROM
tips
INNER JOIN
dune.degentokenbase.result_airdrop_2_tip_allocations allowance
ON allowance.snapshot_date = tips.event_day
AND allowance.fid = tips.donor_fid
WHERE
tips.tip_amount <> 0
),
daily_received_tips AS (
SELECT
event_day,
recipient_fid,
SUM(tip_amount * channel_boost) AS received_amount
FROM
daily_donated_tips
WHERE
donated_amount <= donor_tip_allowance
GROUP BY
event_day,
recipient_fid
),
received_tips AS (
SELECT
recipient_fid AS fid,
SUM(received_amount) AS total_received_amount
FROM
daily_received_tips
GROUP BY
recipient_fid
)
SELECT
rec.fid,
profile.fname,
profile.display_name,
COALESCE(ovrrd.address, verified_eth_addresses.address) AS wallet_address,
ROUND(rec.total_received_amount, 0) AS points,
SUM(ROUND(rec.total_received_amount, 0)) OVER () AS all_received_amount
FROM
received_tips rec
LEFT JOIN
profile
ON profile.fid = rec.fid
AND profile.rn = 1
LEFT JOIN
query_3464901 verified_eth_addresses
ON verified_eth_addresses.fid = rec.fid
LEFT JOIN
query_3423915 ovrrd
ON ovrrd.fid = rec.fid
LEFT JOIN
query_3444616 disqualified
ON disqualified.fid = rec.fid
WHERE
COALESCE(ovrrd.address, verified_eth_addresses.address) IS NOT NULL
AND profile.display_name IS NOT NULL
AND disqualified.fid IS NULL
ORDER BY
rec.total_received_amount DESC
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment