Flipside Community2b) popular apps for first time users
    Updated 2024-02-23
    -- forked from 2b) popular apps for first time users @ https://flipsidecrypto.xyz/edit/queries/4a6093f9-8096-4ef8-8818-87df6a6daa13

    -- forked from 2b @ https://flipsidecrypto.xyz/edit/queries/956394e3-fe4f-4801-b8b3-68e508994b63

    with addresses as
    (
    select from_address, min(block_timestamp) as first_transaction_sent
    from {{Chain}}.core.fact_transactions
    group by from_address
    having first_transaction_sent >= '{{Start_date}}'
    ),
    first_transaction as
    (
    select * from {{Chain}}.core.fact_transactions
    where 1=1
    and block_timestamp >= '{{Start_date}}'
    and from_address in ( select from_address from addresses)
    qualify(row_number() over (partition by from_address order by block_timestamp asc)=1)
    ),
    top_apps as (
    select to_address, count(distinct from_address) as new_user
    from first_transaction ft
    group by to_address
    order by new_user desc
    limit 100
    ),
    top_100 as
    (
    select
    dl.project_name,
    sum(ta.new_user) as number_of_users,
    -- dl.address_name,
    -- dl.label_type,
    -- dl.label_subtype,
    -- dl.project_name,
    row_number() over (order by number_of_users desc) as rn
    QueryRunArchived: QueryRun has been archived