#analytics

Posts tagged #analytics

1. Question

   Daily sales report across timezones: which day does an order at 11:30pm Bangkok belong to?

   We store created_at as timestamptz. Until now all markets were UTC+8 (Singapore, Malaysia) so we just did: select (created_at at time zone 'Asia/Singapore')::date as day, sum(amount) from orders group by 1; Now we add Thailand (UTC+7). Order at 23:30 Bangkok time is 00:30 next day in Singapore. Thai team want it in their day, regional report want one number per day. Options: convert each order using its market…

   Lin WeiOct 14, 20263likes3comments

2. Article

   Why finance and marketing never agree on revenue (checklist from 6 months of pain)

   Every month same meeting. Marketing dashboard say revenue X, finance report say Y, difference 4-8%, and data team (me) is asked to explain. After 6 months I made a checklist. Sharing in case it help someone. Where the numbers split Gross vs net. Marketing count order value at checkout. Finance count after cancellations, refunds and returns. Biggest gap is always this one. Which date. Order date, payment date,…

   Lin WeiSep 27, 20264likes3comments

3. Question

   Postgres count(distinct) on 80M rows very slow, any trick?

   Daily active customers query take ~40s, dashboard timeout: select date_trunc('day', created_at) as day, count(distinct customer_id) from orders where created_at >= now() - interval '90 days' group by 1 order by 1; Table ~80M rows, index on created_at. Postgres 15. EXPLAIN show big Sort spilling to disk. Tried increase work_mem to 256MB, bit faster but still 25s. Is count(distinct) just slow in Postgres? Thinking to…

   Lin WeiSep 13, 20262likes3comments

4. Question

   dbt incremental model getting duplicate rows when late data arrive, how to fix?

   Using dbt on BigQuery. Orders model is incremental, source table get updated when order status change (paid, shipped, refunded). Model look like this: {{ config(materialized='incremental') }} select order_id, status, amount, updated_at from {{ source('shop', 'orders') }} {% if is_incremental() %} where updated_at > (select max(updated_at) from {{ this }}) {% endif %} Problem 1: same order_id appear multiple times…

   Lin WeiAug 28, 20262likes3comments