Insights have different data from the Clickhouse db

Open
#23,271 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
25/100
Issue type
Bug
Clarity
Needs clarification
Activity status
Stale
Tech stack
clickhouse, sql
Domain
analytics, database

Research direction

Start by reproducing the supplied ClickHouse query for the 2024-06-25 date and compare its results with the Trends and SQL insight views. Investigate why adding count(distinct person_id) changes the event count, using the attached screenshots and the self-hosted Docker Compose context. Done means the discrepancy is explained and the affected insight behavior is corrected or clearly documented.

Written by the indexing model from the issue text.

Description

bug
Bug Description

Bug description

While running a query on the Clickhouse client, it gives me results very different from what I get on the Posthog insights. Be it trends or SQL insight. It is giving me a third of what i get from the client.

Here's the query I'm running.

SELECT 
    count(e.uuid),
    count(distinct distinct_id),
    count(distinct person_id)
FROM 
    events e
where
    e.event = '$pageview'
    and e.created_at >= toDateTime('2024-06-25 00:00:00', 'Asia/Kolkata')
    and e.created_at <= toDateTime('2024-06-25 23:59:59', 'Asia/Kolkata')

Additional context

In the screenshots added, please notice that the count for events is 87k in sql and 232k in trends. In the sql insight if i remove the count for person_id the count of event matches the one in trends. This is really bizarre to me.
Running the above query on the clickhouse client gives me

Screenshots for SQL insight and trends are attached:
Screenshot 2024-06-26 at 7 19 33 PM
Screenshot 2024-06-26 at 7 19 41 PM

Screenshot from clickhouse client:
Screenshot 2024-06-27 at 9 59 32 AM

Debug info
- [ ] PostHog Cloud, Debug information: [please copy/paste from https://us.posthog.com/settings/project-details#variables]
- [x] PostHog self-hosted with `docker compose`, version/commit: []
- [ ] PostHog self-hosted with Kubernetes (deprecated, see [`Sunsetting Kubernetes support`](https://posthog.com/blog/sunsetting-helm-support-posthog)), version/commit: [please provide]
Dominant language
Python
Stars
39.9k
Forks
3.4k
Avg merge
7h 27m
Merged PRs (30d)
222

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from PostHog/posthog

All issues in PostHog/posthog

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.