This overview tab utilizes SQL queries to analyze key metrics pertaining to user engagement and trading activity on the Jupiter DEX over the past year.
The focus is on understanding:
๐ Active user base: How many unique users engage with the platform on a daily and monthly basis?
๐ Trading volume: What's the daily and cumulative USD volume traded across the platform?
๐ User stickiness: How consistently do users return to the platform, measured by the daily percentage of MAU actively engaging?
All queries follow a similar structure:
Data Selection: Fetch relevant data from the solana.defi.fact_swaps table, focusing on transactions involving Jupiter (swap_program
ILIKE '%jupiter%').
Time Filtering: Restrict results to the desired timeframe, typically the last 12 months (configurable).
Filtering Successful Swaps: Only include successful swaps in calculations by ensuring succeeded = 'true'.
Date Manipulation: Utilize date_trunc functions to group data by day or month as needed for specific metrics.
Aggregation and Calculations: Perform calculations based on the desired metric, such as counting distinct users, summing volume, or calculating percentages.
Joining and Ordering (Optional): Join results with temporary tables or external data as needed, and finally order results by date for chronological analysis.
Specific Queries:
๐ Daily Active Users (DAU): Counts the number of unique swappers per day.
๐ Monthly Active Users (MAU): Counts the number of unique swappers per month.
๐ Daily Trading Volume: Calculates the daily USD volume traded on the platform.
๐ Cumulative Trading Volume: Calculates the cumulative USD volume traded over the entire timeframe.
๐ Daily Stickiness Percentage: Combines DAU and MAU data to measure the percentage of monthly active users engaging daily.