count(case when topic_0 = '0x17307eab39ab6107e8899845ad3d59bd9653f200f220920489ca2b5937696c31'

then 1 end) as total_listings,

count(case when topic_0 = '0x42c76c81a7cba1b9c861353909a184e20747ab960332628dabcbb5852fc5cbb5'

then 1 end) as total_cancelled_listings,

round(

(count(case when topic_0 = '0x42c76c81a7cba1b9c861353909a184e20747ab960332628dabcbb5852fc5cbb5'

then 1 end) * 100.0)

/ nullif(count(case when topic_0 = '0x17307eab39ab6107e8899845ad3d59bd9653f200f220920489ca2b5937696c31'

then 1 end), 0),

2) as cancel_percentage

from monad.testnet.fact_event_logs

where contract_address in (

lower('0x04edB399CC24a95672BF9B880EE550dE0b2D0B1e'), -- Listing Contract

lower('0xfA4D5a9ceA2822BA08d0266F121011aC527ced64') -- Cancelled Listing Contract

and topic_0 in (

'0x17307eab39ab6107e8899845ad3d59bd9653f200f220920489ca2b5937696c31', -- Listings

'0x42c76c81a7cba1b9c861353909a184e20747ab960332628dabcbb5852fc5cbb5' -- Cancellations

);

SELECT 'Total Listings' AS activity,

COUNT(*) FILTER (WHERE topic_0 = '0x17307eab39ab6107e8899845ad3d59bd9653f200f220920489ca2b5937696c31') AS value

UNION ALL

SELECT 'Total Cancelled Listings' AS activity,

COUNT(*) FILTER (WHERE topic_0 = '0x42c76c81a7cba1b9c861353909a184e20747ab960332628dabcbb5852fc5cbb5') AS value

UNION ALL

SELECT 'Cancel Percentage' AS activity,

ROUND(

(COUNT(*) FILTER (WHERE topic_0 = '0x42c76c81a7cba1b9c861353909a184e20747ab960332628dabcbb5852fc5cbb5') * 100.0)

/ NULLIF(COUNT(*) FILTER (WHERE topic_0 = '0x17307eab39ab6107e8899845ad3d59bd9653f200f220920489ca2b5937696c31'), 0),

2) AS value

FROM monad.testnet.fact_event_logs