Methodology:

Daily Prices Calculation:
- For each token, the average closing price is calculated on a daily basis.
- This is done to later convert daily sales and commissions into USD for tokens other than USDC.
Listing Indexer:
- For each transaction, the date, buyer, transaction count, currency type, daily sale price, and daily commission are determined.
- The currency type is determined based on the EVENT_DATA:salePaymentVaultType column. If it matches certain predefined strings, it's categorized as 'Flow', 'USDC', or 'Other'.
- The daily sale price and daily commission are aggregated for each date, buyer, and currency type.
Buyer's First Transaction:
- For each buyer, the date of their first transaction is identified.
Final Aggregation:
- The data from the Listing Indexer is joined with the Buyer's First Transaction data to differentiate between new and returning buyers.
- The daily prices table is used to convert daily sales and commissions into USD.
- Various metrics like daily transactions, unique buyers, new buyers, returning buyers, daily sales, daily commissions, and their cumulative values are calculated.

Tables Used:

flow.core.fact_hourly_prices:
- Contains hourly price data for different tokens.
- Used to calculate the average daily price for each token.
FLOW.CORE.FACT_EVENTS:
- Contains event data related to transactions.
- Used to extract transaction details, including the buyer, transaction date, sale price, commission, and currency type.

Daily Prices: The average closing price of tokens on a daily basis.
Listing Indexer: A derived table that contains aggregated transaction data, including the buyer, transaction count, currency type, daily sale price, and daily commission.
Buyer's First Transaction: A derived table that identifies the date of the first transaction for each buyer.
Currency: The type of currency used in the transaction. It can be 'Flow', 'USDC', or 'Other' based on the EVENT_DATA:salePaymentVaultType column.
Daily Sale Price: The total sale price for transactions on a particular day.
Daily Commission: The total commission amount for transactions on a particular day.
New Buyers: Buyers who made their first transaction on a particular day.
Returning Buyers: Buyers who made transactions on a particular day but had made their first transaction on an earlier date.
Cumulative Metrics: The running total of various metrics, like transactions, sales, commissions, etc., up to a particular date.