jp12Flash [SOL] Saga - Num orders
    Updated 2023-09-09
    -- SELECT COUNT(DISTINCT tx_id) as num_orders, 100 * num_orders as amount
    -- -- FROM solana.core.fact_transactions
    -- FROM solana.core.fact_events
    -- -- WHERE tx_id = 'MuQC1cGUVyeLD1AMvTFZSpJULMKAtbQbPuF921cZVLWuagb1CJZ9DdNe29MzJ6hnmb1YpZWgBKjzC1mkfXwetNx'
    -- WHERE block_timestamp >= '2022-06-22' and PROGRAM_ID = '781wH11JGQgEoBkBzuc8uoQLtp8KxeHk1yZiS1JhFYKy'
    -- and SUCCEEDED = 'TRUE' and INNER_INSTRUCTION:instructions[1]:parsed:info:amount = '100000000'

    WITH tx as (
    SELECT INNER_INSTRUCTION:instructions[1]:parsed:info:authority::string as user, 1 as num_order
    -- FROM solana.core.fact_transactions
    FROM solana.core.fact_events
    -- WHERE tx_id = 'MuQC1cGUVyeLD1AMvTFZSpJULMKAtbQbPuF921cZVLWuagb1CJZ9DdNe29MzJ6hnmb1YpZWgBKjzC1mkfXwetNx'
    WHERE block_timestamp >= '2022-06-22' and PROGRAM_ID = '781wH11JGQgEoBkBzuc8uoQLtp8KxeHk1yZiS1JhFYKy'
    and SUCCEEDED = 'TRUE' and INNER_INSTRUCTION:instructions[1]:parsed:info:amount = '100000000'


    UNION

    SELECT INNER_INSTRUCTION:instructions[1]:parsed:info:authority::string as user, -1 as num_order
    -- FROM solana.core.fact_transactions
    FROM solana.core.fact_events
    -- WHERE tx_id = 'MuQC1cGUVyeLD1AMvTFZSpJULMKAtbQbPuF921cZVLWuagb1CJZ9DdNe29MzJ6hnmb1YpZWgBKjzC1mkfXwetNx'
    WHERE block_timestamp >= '2022-06-22' and PROGRAM_ID = '781wH11JGQgEoBkBzuc8uoQLtp8KxeHk1yZiS1JhFYKy'
    and SUCCEEDED = 'TRUE' and INNER_INSTRUCTION:instructions[0]:parsed:info:amount = '100000000'
    )

    SELECT SUM(num_order) as total_orders, 100 * total_orders as amount
    FROM tx
    -- GROUP BY 1
    HAVING total_orders > 0
    ORDER BY total_orders DESC
    Run a query to Download Data