In addition to MoM Cohort Retention, a great measure of a products stickiness is the Stickiness Ratio (SR). SR is one of the key product metrics you should measure to understand whether your users are returning on a regular basis. The following query calculates SR by finding what percentage of monthly active users are daily active users (DAU/MAU).

With daily_active_users as (
SELECT date_trunc('month', day) as date, avg(active_addresses) as avg_dau
FROM (
    SELECT date_trunc('day', signed_at) as day, uniq(tx_sender) AS active_addresses
    FROM blockchains.all_chains
    WHERE chain_name = 'avalanche_mainnet'
    AND tx_recipient = unhex('82a85407BD612f52577909F4A58bfC6873f14DA8')
    AND [signed_at:daterange]
    GROUP BY day
		)
GROUP BY date
),
monthly_active_users as (
		SELECT date_trunc('month', signed_at) as date, uniq(tx_sender) AS mau
    FROM blockchains.all_chains
    WHERE chain_name = 'avalanche_mainnet'
    AND tx_recipient = unhex('82a85407BD612f52577909F4A58bfC6873f14DA8')
    AND [signed_at:daterange]
		GROUP BY date
)
SELECT daily.date as date, (daily.avg_dau/monthly.mau) as stickiness_ratio
FROM daily_active_users daily
  LEFT JOIN monthly_active_users monthly
  	ON daily.date = monthly.date
ORDER BY date
      

Want to learn how we got there? 👇

The following query will retrieve all the entries in the transactions data table.

SELECT * FROM blockchains.all_chains LIMIT 10
-- Too much data - lets refine it

Our goal is to transform this query into one that measures a projects stickiness ratio - daily active users/monthly active users (DAU/MAU). The stickiness ratio is calculated by dividing the total active addresses in a month by the average number of daily active addresses. To do so you need to query the unique addresses in the tx_sender column where the tx_recipient is the Crabada: Game contract. As you can see in the image below, the Crabada: Game contract is always the recipient for in game transactions (to column).

State the following in the WHERE statement - learn about the unhex() function here.

WHERE tx_recipient = unhex('82a85407BD612f52577909F4A58bfC6873f14DA8')

To then optimise your query, add:

1. AND chain_name = 'avalanche_mainnet' so your query isn’t searching through data on other chains.
2. AND signed_at > ‘2021-11-01’ which is the date Crabada first started.

SELECT tx_sender 
FROM blockchains.all_chains 
WHERE tx_recipient = unhex('82a85407BD612f52577909F4A58bfC6873f14DA8')
AND chain_name = 'avalanche_mainnet'
AND [signed_at:daterange]

There are two SELECT statements that need to be added to find the daily count of tx_sender addresses.

1. date_trunc(’day’, signed_at) as day to select unique addresses by the day that they were active.
2. uniq(tx_sender) AS active_addresses counts the number of unique addresses that were active. Learn about the uniq() function here.

SELECT date_trunc(’day’, signed_at) as day
	, uniq(tx_sender) as active_addresses
FROM blockchains.all_chains 
WHERE tx_recipient = unhex('82a85407BD612f52577909F4A58bfC6873f14DA8')
AND chain_name = 'avalanche_mainnet'
AND [signed_at:daterange]
GROUP BY day

Now you have a long list of days and the corresponding active addresses.

However, we want to aggregate the date column by month and find the average number of daily active addresses. To do this you must turn the SQL above into a subquery - a query that lives within another query.