Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Unique Visits always shows equal to Total Visitors #38

Open
ScotterC opened this issue Dec 13, 2023 · 0 comments
Open

Unique Visits always shows equal to Total Visitors #38

ScotterC opened this issue Dec 13, 2023 · 0 comments

Comments

@ScotterC
Copy link

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant