ahoy_captain
ahoy_captain copied to clipboard
Unique Visits always shows equal to Total Visitors
On my deployment these numbers are always the same even though when they should be different. I've verified the results being different for a given month when comparing these queries:
# @presenter.unique_visitors
SELECT SUM(count_visitor_token) AS total_unique_visitors
FROM (
SELECT COUNT(DISTINCT "ahoy_visits"."visitor_token") AS "count_visitor_token",
DATE_TRUNC('day', "ahoy_visits"."started_at"::timestamptz AT TIME ZONE 'America/New_York')::date AS "date_trunc_day"
FROM "ahoy_visits"
LEFT OUTER JOIN "ahoy_events" ON "ahoy_events"."visit_id" = "ahoy_visits"."id"
WHERE ("ahoy_visits"."started_at" > '2023-11-13 00:01:00' AND "ahoy_visits"."started_at" < '2023-12-13 14:20:00')
AND ("ahoy_events"."time" > '2023-11-13 00:01:00' AND "ahoy_events"."time" < '2023-12-13 14:20:00')
AND ("ahoy_visits"."started_at" IS NOT NULL)
GROUP BY DATE_TRUNC('day', "ahoy_visits"."started_at"::timestamptz AT TIME ZONE 'America/New_York')::date
) AS daily_counts;
vs
# @presenter.total_visits
WITH
"current" AS (
SELECT COUNT(DISTINCT "ahoy_visits"."id")
FROM "ahoy_visits"
LEFT OUTER JOIN "ahoy_events"
ON "ahoy_events"."visit_id" = "ahoy_visits"."id"
WHERE ("ahoy_visits"."started_at" > '2023-11-13 00:01:00'
AND "ahoy_visits"."started_at" < '2023-12-13 14:20:00')
AND ("ahoy_events"."time" > '2023-11-13 00:01:00'
AND "ahoy_events"."time" < '2023-12-13 14:20:00')
),
"compare" AS (
SELECT COUNT(DISTINCT "ahoy_visits"."id")
FROM "ahoy_visits"
LEFT OUTER JOIN "ahoy_events"
ON "ahoy_events"."visit_id" = "ahoy_visits"."id"
WHERE ("ahoy_visits"."started_at" > '2023-10-13 09:42:00'
AND "ahoy_visits"."started_at" < '2023-11-13 00:01:00')
AND ("ahoy_events"."time" > '2023-10-13 09:42:00'
AND "ahoy_events"."time" < '2023-11-13 00:01:00')
)
SELECT
current,
compare
FROM
current,
compare;
I'm having a really hard diagnosing where the problem is in the ViewComponents. At the Stats level the queries look fine which is how I derived the ones above but I got lost in ComparableContainerComponent and ran out of time for this problem today.