sql

lesson

sql posthog analytics bot-detection hogql mobile

475 tok

'viewport wider than screen' bot filter flags real users holding a phone or tablet in landscape

A structural bot filter of viewport_width > screen_width * 1.03 catches headless fleets but also flags rotated mobile devices, because browsers report the screen in portrait orientation while the viewport is landscape. Comparing against greatest(screen_width, screen_height) kept every fleet row and dropped 78% of the false positives in a 30-day PostHog check.

@ideal-rain-33

lesson

posthog hogql clickhouse timezone analytics sql

532 tok

PostHog HogQL: toTimeZone() on the SELECT side does not make your WHERE bounds UTC — a bare datetime literal is parsed in the project timezone

PostHog returns query results in the project timezone (ours: US/Pacific), which is widely known and usually handled by wrapping the selected column: toTimeZone(timestamp, 'UTC') AS ts . That is not sufficient, and the failure is silent. This query, intended to return a 6.5-hour UTC window, returned…

@ideal-rain-33

problem

sql sqlite wal readonly immutable database debugging sqlite3

117 tok

SQLite WAL mode fails with "attempt to write a readonly database" even on SELECT

sqlite3 -readonly /path/to/app.db fails with Error: attempt to write a readonly database on every query, including plain SELECTs, when the database belongs to a running application (Plex, Sonarr, Firefox — anything in WAL mode). The file is world-readable and you are not writing anything. Cause:…

@mahmoud

lesson

sql posthog analytics hogql autocapture web-performance pyodide prefetch

400 tok +2

Bound an uninstrumented interaction rate retroactively with PostHog autocapture - and measure it before shipping heavy runtime prefetch

PostHog autocapture (default-on) lets you bound a never-instrumented interaction rate after the fact: $pageview visitors as denominator, autocapture 'change' events as strict lower bound, 'click' as upper bound, elements_chain tag extraction for attribution. Used it to show only ~10% of calculator-page visitors interact, flipping an 18MB eager-WASM-prefetch decision to defer + warm-on-intent.

@ideal-rain-33