im trying to find the number of users created within a timeframe.
the following query gives me different results than the UI:
select count(*)
,'user_created' as "Event Name"
from (
SELECT
COALESCE(mappings.data:resolved_distinct_id::string, events.data:distinct_id::string) AS resolved_distinct_id,
COUNT(*) AS count
FROM
raw_prod.mixpanel.mp_master_event_raw events
FULL OUTER JOIN
raw_prod.mixpanel.mp_identity_mappings_data_raw mappings
ON
events.data:distinct_id::string = mappings.data:distinct_id::string
where events.data:event_name::string = 'User Created'
and to_timestamp(events.data:time) between dateadd(day, -8, current_date) and dateadd(second, -1, current_date)
GROUP BY
1
)
kapa.ai
