What makes a Top Shots moment valuable? (Part I)

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.

SQL Query for Play Type by Number of Transactions

SELECT 
  play_type,
  COUNT (*) AS no_of_txns
FROM flow.core.dim_topshot_metadata
GROUP BY play_type
ORDER BY no_of_txns DESC

Top 5 Play Types by Number of Transactions

In the below graph we can see the top 5 play types by the number of transactions.

Play Type Rim is the highest contributor of 44.42% to the total number of transactions. The highest number of transactions is 4.28L in Rim and the lowest total number of transactions is 1.19L in Mid-Range.

Play Types wise Number of Transactions

In the below graph, we can see the play types wise number of transactions.

Rim Play Types contributed 40.42% of the total number of transactions. the highest total number of transactions is 4.28L in Rim and the lowest total number of transactions is 298 2 Pointer

SQL Query for Number of Transaction By Play Type Across each Day

WITH nft_sales as (
SELECT
  PLAY_TYPE, 
  s.NFT_ID,
  s.Price,
  TX_ID,
  BLOCK_TIMESTAMP
FROM flow.core.fact_nft_sales s
JOIN flow.core.dim_topshot_metadata m ON s.NFT_ID = m.NFT_ID
where TX_SUCCEEDED = TRUE
),
  rim as(
   SELECT date_trunc('day',BLOCK_TIMESTAMP)as date, 
      count(DISTINCT TX_ID)  as rim_count,
      sum(Price) as rim_volume
  FROM nft_sales 
  WHERE  PLAY_TYPE = 'Rim'
  GROUP BY 1
  ),
 three_pointer as(
   SELECT date_trunc('day',BLOCK_TIMESTAMP)as date, 
      count(DISTINCT TX_ID) as three_pointer_count,
      sum(Price) as three_pointer_volume
   FROM nft_sales 
   WHERE  PLAY_TYPE = '3 Pointer'
   GROUP BY 1 
  ),
  assist as (
   SELECT date_trunc('day',BLOCK_TIMESTAMP)as date, 
     count(DISTINCT TX_ID)  as assist_count,
      sum(Price) as assist_volume
   FROM nft_sales 
   WHERE  PLAY_TYPE = 'Assist'
   GROUP BY 1
  ),
 mid_range as (
   SELECT date_trunc('day',BLOCK_TIMESTAMP)as date, 
     count(DISTINCT TX_ID) as mid_range_count,
     sum(Price) as mid_range_volume
   FROM nft_sales 
   WHERE  PLAY_TYPE = 'Mid-Range'
   GROUP BY 1
  ),
  steal as (
   SELECT date_trunc('day',BLOCK_TIMESTAMP)as date, 
      count(DISTINCT TX_ID) as steal_count,
      sum(Price) as steal_volume
  FROM nft_sales 
  WHERE  PLAY_TYPE = 'Steal'
  GROUP BY 1
  ),
  handles as (
   SELECT date_trunc('day',BLOCK_TIMESTAMP)as date, 
     count(DISTINCT TX_ID) as handles_count,
     sum(Price) as handles_volume
  FROM nft_sales 
  WHERE  PLAY_TYPE = 'Handles'
  GROUP BY 1
  ),
 dunk_layup as (
   SELECT date_trunc('day',BLOCK_TIMESTAMP)as date, 
     count(DISTINCT TX_ID)  as dunk_layup_count,
     sum(Price) as dunk_layup_volume
  FROM nft_sales 
  WHERE  PLAY_TYPE = 'Dunk/Layup'
  GROUP BY 1
  ),
  two_pointer as (
   SELECT date_trunc('day',BLOCK_TIMESTAMP)as date, 
     count(DISTINCT TX_ID) as two_pointer_count,
     sum(Price) as two_pointer_volume
   FROM nft_sales 
   WHERE  PLAY_TYPE = '2 Pointer'
   GROUP BY 1
  )
  
SELECT rim.date, 
  rim_count,rim_volume,
  three_pointer_count,three_pointer_volume,
  mid_range_count,mid_range_volume,
  steal_count,steal_volume,
  handles_count,handles_volume,
  dunk_layup_count,dunk_layup_volume,
  two_pointer_count,two_pointer_volume  
FROM rim 
JOIN three_pointer ON rim.date = three_pointer.date
JOIN assist ON rim.date = assist.date
JOIN mid_range ON rim.date = mid_range.date
JOIN steal ON rim.date = steal.date
JOIN handles ON rim.date = handles.date
JOIN dunk_layup ON rim.date = dunk_layup.date
JOIN two_pointer ON rim.date = two_pointer.date

Number of Transactions By Play Type Across each Day

In the below graph, we can see the total number of transactions by Play Type Across each day since April 2022.

Rim Play type has observed more number of transactions compared to the all other