primo_datatop_shot_moments_over_time
Updated 2022-07-22
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
--Create an analysis on NBA Top Shots moments and attempt to uncover any correlations between a specific category and sales volume.
--This week, focus specifically on the play_type column in the flow.core.dim_topshot_metadata table.
--What play_types do users hold most, and how much volume in sales do these play_types generate? Include any other transactional metrics you can think of.
-- https://app.flipsidecrypto.com/dashboard/volume-scoring-with-nba-top-shot-hKa6dv
with flow_usd as (
select date(timestamp) dt
, avg(price_usd) flow_usd
from flow.core.fact_prices
where symbol = 'FLOW'
group by 1
),
sales as (
select
date(s.block_timestamp) dt
, d.PLAY_TYPE
, s.tx_id
, s.nft_id
, s.buyer
, s.seller
, (s.price * flow_usd.flow_usd) sale_usd
from flow.core.fact_nft_sales s
inner join flow.core.dim_topshot_metadata d
on s.nft_id = d.nft_id
left join flow_usd
on flow_usd.dt = date(s.block_timestamp)
where s.tx_succeeded = TRUE
and s.nft_collection = 'A.0b2a3299cc857e29.TopShot'
and d.play_type not in ('Dunk/Layup','2 Pointer') -- excluding b/c <0.1% of NFTs
)
select
dt
, PLAY_TYPE
, count(distinct tx_id) sales_ct
, count(distinct nft_id) nft_ct
, count(distinct buyer) unique_buyers
Run a query to Download Data