SQL Queries for User Analytics (MySQL Examples)

You have events landing in a table, and now someone asks, “How many people came back last week?” Raw rows cannot answer that. You need SQL queries for user analytics that turn a stream of clicks into counts of people, sessions, conversions and returning users.

This article gives you those queries for MySQL 8.4. Every query runs against the Acme Shop event shape from earlier in the series, so you can paste it into a client and check the output against a ten-row dataset by hand. If you have not captured events yet, start with tracking button clicks and form submissions with JavaScript, and with centralizing server logs if your data arrives from the server side.

You will build a small events table, a view that fixes the “who is this person” problem, and then queries for daily and monthly active users, top pages, sessions, funnels and retention cohorts. The last section covers window functions, which make half of these queries shorter. Schema design belongs to PostgreSQL schema design for analytics, and index and partition tuning belongs to PostgreSQL performance tuning for analytics. This article stays on the queries.

Create the MySQL events table

Article 4 is the one place in this series that uses MySQL. The table has the same shape as the PostgreSQL events table that later articles build, except that properties is a MySQL JSON column. All timestamps are UTC.

CREATE TABLE events (
  event_id     CHAR(36)      NOT NULL,
  event_name   VARCHAR(64)   NOT NULL,
  occurred_at  DATETIME(3)   NOT NULL,
  anonymous_id VARCHAR(64)   NOT NULL,
  user_id      VARCHAR(64)   NULL,
  session_id   VARCHAR(64)   NOT NULL,
  page_url     VARCHAR(2048) NOT NULL,
  referrer     VARCHAR(2048) NULL,
  user_agent   VARCHAR(512)  NULL,
  properties   JSON          NULL,
  PRIMARY KEY (event_id),
  KEY idx_events_occurred_at (occurred_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Two details matter here. The primary key on event_id makes retries harmless, because a second insert of the same UUID fails or gets ignored. The single index on occurred_at keeps date-range scans reasonable for this article. Real workloads need more, and that discussion lives in the tuning article.

In my experience building event pipelines, the first reporting bug is almost always duplicate rows. We once double-counted purchases because a client retried a batch after a timeout that had actually succeeded. Revenue in the dashboard drifted above the payment provider’s records, and nobody noticed until finance compared the two. The fix was the primary key above plus INSERT IGNORE in the collector. No query changed.

Seed a ten-row dataset you can verify by hand

Never test analytical SQL on production data first. A tiny dataset lets you predict the answer before you run the query. If your prediction and the result differ, the query is wrong, not the data.

INSERT INTO events
  (event_id, event_name, occurred_at, anonymous_id, user_id, session_id, page_url, referrer, properties)
VALUES
  (UUID(), 'page_view',          '2025-10-01 09:00:00.000', 'a1', NULL, 's1', 'https://acme-shop.example/products/42',    'https://www.google.com/', NULL),
  (UUID(), 'add_to_cart',        '2025-10-01 09:05:00.000', 'a1', NULL, 's1', 'https://acme-shop.example/products/42',    NULL, '{"product_id": 42}'),
  (UUID(), 'purchase_completed', '2025-10-01 09:10:00.000', 'a1', NULL, 's1', 'https://acme-shop.example/checkout/thanks', NULL, '{"order_total": 59.90}'),
  (UUID(), 'page_view',          '2025-10-01 10:00:00.000', 'a2', NULL, 's2', 'https://acme-shop.example/',               'https://www.google.com/', NULL),
  (UUID(), 'add_to_cart',        '2025-10-01 10:03:00.000', 'a2', NULL, 's2', 'https://acme-shop.example/products/42',    NULL, '{"product_id": 42}'),
  (UUID(), 'page_view',          '2025-10-01 11:00:00.000', 'a3', NULL, 's3', 'https://acme-shop.example/products/42',    NULL, NULL),
  (UUID(), 'page_view',          '2025-10-02 08:00:00.000', 'a1', 'u-100', 's4', 'https://acme-shop.example/',               NULL, NULL),
  (UUID(), 'page_view',          '2025-10-02 12:00:00.000', 'a3', NULL, 's5', 'https://acme-shop.example/products/7',     NULL, NULL),
  (UUID(), 'page_view',          '2025-10-03 07:00:00.000', 'a2', NULL, 's6', 'https://acme-shop.example/products/42',    NULL, NULL),
  (UUID(), 'page_view',          '2025-10-03 07:30:00.000', 'a3', NULL, 's7', 'https://acme-shop.example/',               NULL, NULL);

Three browsers (a1, a2, a3) generate seven page views over three days. One person buys, and that same person logs in on day two as u-100, so one human has two identities. Keep this table in mind, because every expected result below comes from it.

Count people, not cookies: identity and time zones

An event has an anonymous_id for the browser and a user_id that stays null until login. The same human therefore appears under two identities if you count one of them naively. Consider the wrong query first.

-- WRONG: a person who logs in mid-week counts twice
SELECT COUNT(DISTINCT COALESCE(user_id, anonymous_id)) AS people
FROM events;

Before login, the person’s events carry only anonymous_id. After login, they carry user_id. On the seed data the query returns 4 for what is really 3 people, because browser a1 appears once as a1 and once as u-100. The fix is a view that maps each anonymous id to the user id it later resolved to.

CREATE VIEW events_identified AS
SELECT e.*,
       COALESCE(m.user_id, e.anonymous_id) AS person_key
FROM events e
LEFT JOIN (
  SELECT anonymous_id, MAX(user_id) AS user_id
  FROM events
  WHERE user_id IS NOT NULL
  GROUP BY anonymous_id
) m ON m.anonymous_id = e.anonymous_id;

Now every row has a person_key that is the user id when known and the browser id otherwise. Every a1 row now carries u-100, and the count drops to the correct 3. The delta is simple: identity is resolved once, in one place, and every later query counts person_key. This approach has a cost. MAX(user_id) picks an arbitrary winner if one browser logs into two accounts, such as a shared family laptop. Accept that error or build a proper identity table, which the schema design article covers.

Time is the second trap. Store UTC, then decide the reporting time zone once. A common bug is filtering a day with BETWEEN and a made-up end time.

-- WRONG: misses events at 23:59:59.500
SELECT COUNT(*) FROM events
WHERE occurred_at BETWEEN '2025-10-01 00:00:00' AND '2025-10-01 23:59:59';

-- RIGHT: half-open range, no gaps and no overlap
SELECT COUNT(*) FROM events
WHERE occurred_at >= '2025-10-01' AND occurred_at < '2025-10-02';

On the seed data both queries return 6, because no event lands in the last second of the day. Insert one at 23:59:59.500 and only the half-open version counts it. The half-open range works for any precision, including the millisecond timestamps in this table. For reports in a local zone, use CONVERT_TZ(occurred_at, 'UTC', 'America/New_York'). Named zones only work after you load the time zone tables with mysql_tzinfo_to_sql, so test that before you ship a report.

Daily, weekly and monthly active users

Daily active users (DAU) counts distinct people who did anything in a day. Group by the date and count person_key, never rows.

SELECT DATE(occurred_at)           AS day,
       COUNT(DISTINCT person_key)  AS dau
FROM events_identified
GROUP BY DATE(occurred_at)
ORDER BY day;

On the seed data, the answer is 3 for 2025-10-01, 2 for 2025-10-02 and 2 for 2025-10-03. Check it by eye: browsers a1, a2 and a3 are active on the first day, then a1 and a3, then a2 and a3.

Monthly active users (MAU) needs a rolling window, and this is where people reach for window functions and get stuck. MySQL does not accept DISTINCT inside a window aggregate, so a rolling distinct count cannot use COUNT(DISTINCT x) OVER. A correlated subquery per day is clear and correct.

WITH days AS (
  SELECT DISTINCT DATE(occurred_at) AS day FROM events
)
SELECT t.day, t.dau, t.mau,
       ROUND(100 * t.dau / t.mau, 1) AS stickiness_pct
FROM (
  SELECT d.day,
    (SELECT COUNT(DISTINCT e.person_key)
       FROM events_identified e
      WHERE e.occurred_at >= d.day
        AND e.occurred_at <  d.day + INTERVAL 1 DAY) AS dau,
    (SELECT COUNT(DISTINCT e.person_key)
       FROM events_identified e
      WHERE e.occurred_at >= d.day - INTERVAL 29 DAY
        AND e.occurred_at <  d.day + INTERVAL 1 DAY) AS mau
  FROM days d
) t
ORDER BY t.day;

Here “MAU” means distinct people in the trailing 30 days, which is how most product teams use it. Stickiness, DAU divided by MAU, tells you how habitual the product is. The seed data gives 100.0, 66.7 and 66.7. Change 29 DAY to 6 DAY for weekly active users.

The trade-off is cost. Each day triggers a scan of thirty days of events, so a year of reporting scans thirty times the data. That is fine for a million rows. Beyond that, compute one row per person per day into a rollup and query the rollup instead.

Top pages and top referrers

A raw page_url includes query strings, so /products/42?utm_source=mail and /products/42 look like different pages. Strip the host and query first, then count. Use a CTE so the path expression appears once. The expression assumes https:// URLs, as in the canonical event shape.

WITH pv AS (
  SELECT person_key,
         SUBSTRING_INDEX(SUBSTRING(page_url, LOCATE('/', page_url, 9)), '?', 1) AS path
  FROM events_identified
  WHERE event_name = 'page_view'
)
SELECT path,
       COUNT(*)                   AS views,
       COUNT(DISTINCT person_key) AS visitors
FROM pv
GROUP BY path
ORDER BY views DESC, path
LIMIT 10;

The seed data returns / with 3 views, /products/42 with 3 views and /products/7 with 1. Notice that views and visitors differ in general. A page viewed ten times by one person is ten views and one visitor. Reports that mix the two confuse every stakeholder eventually.

Referrers follow the same pattern. Extract the host with two nested SUBSTRING_INDEX calls, and label empty referrers as direct traffic.

SELECT COALESCE(NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(referrer, '/', 3), '/', -1), ''), '(direct)') AS source,
       COUNT(*) AS page_views
FROM events
WHERE event_name = 'page_view'
GROUP BY source
ORDER BY page_views DESC;

You get (direct) with 5 and www.google.com with 2. Keep in mind that a missing referrer is not always direct traffic. Privacy settings, in-app browsers and HTTPS-to-HTTP hops all strip it, so treat the direct bucket as “unknown”.

JSON properties follow the same logic. The ->> operator extracts and unquotes a value, as the MySQL JSON search function reference documents. Cast it before you do arithmetic:

SELECT DATE(occurred_at) AS day,
       SUM(CAST(properties->>'$.order_total' AS DECIMAL(10,2))) AS revenue
FROM events
WHERE event_name = 'purchase_completed'
GROUP BY DATE(occurred_at);

The seed data returns 59.90 for 2025-10-01. Skipping the cast looks harmless for SUM, because MySQL converts the strings to floating point. The trap is everything else: MAX, ORDER BY and comparisons treat the unquoted value as text, so 9.5 sorts above 59.9. Cast once, in the expression, and you get exact decimal arithmetic too.

Sessions: count, duration and bounce rate

The browser assigns session_id with a 30-minute inactivity window, so a session query is a plain GROUP BY. A session is not a user and not a visit to a single page. It is a bundle of events from one browser in one burst of activity. This table declares session_id as NOT NULL because the browser always supplies one. Rows rebuilt from server logs, as in the log centralization article, have no browser session, so assign them a derived session at load time or keep them out of session queries.

WITH s AS (
  SELECT session_id,
         MIN(occurred_at) AS started_at,
         TIMESTAMPDIFF(SECOND, MIN(occurred_at), MAX(occurred_at)) AS duration_s,
         COUNT(*) AS events
  FROM events
  GROUP BY session_id
)
SELECT DATE(started_at)            AS day,
       COUNT(*)                    AS sessions,
       SUM(events = 1)             AS bounced,
       ROUND(100 * AVG(events = 1), 1) AS bounce_rate_pct,
       ROUND(AVG(duration_s))      AS avg_duration_s
FROM s
GROUP BY DATE(started_at)
ORDER BY day;

MySQL treats a comparison as 1 or 0, so SUM(events = 1) counts single-event sessions. On the seed data, 2025-10-01 has 3 sessions and 1 bounce (33.3 percent), and the next two days have 100.0 percent. Define “bounce” in writing before you publish the number. A single-event session is the simplest definition and the easiest to reproduce.

Sometimes you cannot trust the client’s session ids. Maybe you are backfilling old logs, or two clients disagree. In that case, rebuild sessions in SQL with LAG and a running sum, which is the classic sessionization pattern.

WITH ordered AS (
  SELECT person_key, occurred_at,
         LAG(occurred_at) OVER (PARTITION BY person_key ORDER BY occurred_at) AS prev_at
  FROM events_identified
), flagged AS (
  SELECT person_key, occurred_at,
         CASE WHEN prev_at IS NULL
                OR TIMESTAMPDIFF(SECOND, prev_at, occurred_at) > 1800
              THEN 1 ELSE 0 END AS is_new_session
  FROM ordered
)
SELECT person_key, occurred_at,
       SUM(is_new_session) OVER (PARTITION BY person_key ORDER BY occurred_at) AS session_n
FROM flagged
ORDER BY person_key, occurred_at;

The logic has three steps. LAG finds the previous event time for the same person. A gap over 1800 seconds flags a new session. The running sum of flags numbers the sessions. For u-100 (browser a1), the 09:00 to 09:10 events share session 1, and the next-day view starts session 2. The MySQL window function reference lists every function used here.

Funnels: from page view to purchase

A funnel counts how many people reach each step in order. The usual mistake is counting events per step. That overcounts anyone who repeats a step and ignores order entirely.

-- WRONG: counts events, ignores people and order
SELECT event_name, COUNT(*) AS n
FROM events
WHERE event_name IN ('page_view', 'add_to_cart', 'purchase_completed')
GROUP BY event_name;

Compute each person’s first time at every step, then compare times. A step counts only if it happened after the previous one.

WITH steps AS (
  SELECT person_key,
         MIN(CASE WHEN event_name = 'page_view'          THEN occurred_at END) AS t_view,
         MIN(CASE WHEN event_name = 'add_to_cart'        THEN occurred_at END) AS t_cart,
         MIN(CASE WHEN event_name = 'purchase_completed' THEN occurred_at END) AS t_buy
  FROM events_identified
  WHERE occurred_at >= '2025-10-01' AND occurred_at < '2025-10-08'
  GROUP BY person_key
)
SELECT COUNT(t_view)                                                       AS viewed,
       SUM(t_cart >= t_view)                                               AS added_to_cart,
       SUM(t_buy >= t_cart AND t_cart >= t_view)                           AS purchased,
       ROUND(100 * SUM(t_buy >= t_cart AND t_cart >= t_view) / COUNT(t_view), 1) AS conversion_pct
FROM steps;

Comparisons against NULL evaluate to NULL, and SUM ignores those, so people who skipped a step drop out automatically. The seed data returns 3 viewers, 2 who added to cart, and 1 purchaser, a 33.3 percent conversion. Add AND t_buy <= t_view + INTERVAL 7 DAY if your business defines conversion inside a time window.

The cost: this form uses each person’s first event per step, so someone who views, buys, then views again still counts correctly, but strict ordering across repeated loops is lost. For funnels with many steps or branching, the SQL gets long and a purpose-built engine wins. That is a real reason teams adopt a product such as PostHog or Mixpanel, and it is fine to buy when funnels are your main workload.

Retention cohorts

Retention answers “of the people who started on day X, how many came back on day X plus N?” Build it in two moves. First find each person’s first active day, which defines their cohort. Then join to every day they were active and compute the day offset.

WITH firsts AS (
  SELECT person_key, MIN(DATE(occurred_at)) AS cohort_day
  FROM events_identified
  GROUP BY person_key
), activity AS (
  SELECT DISTINCT person_key, DATE(occurred_at) AS active_day
  FROM events_identified
), cohorts AS (
  SELECT f.cohort_day,
         DATEDIFF(a.active_day, f.cohort_day) AS day_n,
         COUNT(*) AS users
  FROM firsts f
  JOIN activity a ON a.person_key = f.person_key
  GROUP BY f.cohort_day, DATEDIFF(a.active_day, f.cohort_day)
)
SELECT cohort_day, day_n, users,
       ROUND(100 * users / FIRST_VALUE(users) OVER (PARTITION BY cohort_day ORDER BY day_n), 1) AS retained_pct
FROM cohorts
ORDER BY cohort_day, day_n;

For the seed data, the 2025-10-01 cohort has 3 people on day 0, 2 on day 1 (u-100 and a3), and 2 on day 2 (a2 and a3). Percentages are 100.0, 66.7 and 66.7. Day 0 always equals the cohort size, which gives you a built-in sanity check.

For weekly cohorts, replace the date with the Monday of its week: DATE_SUB(d, INTERVAL WEEKDAY(d) DAY). Then divide the day difference by seven. Two traps appear in production. Incomplete recent cohorts look like churn, because day 7 has not happened yet for last week’s signups. Hide or grey out cells whose offset exceeds the data’s end date.

Window functions for running totals and rankings

Window functions compute across related rows without collapsing them, and MySQL has supported them since version 8.0. Three patterns cover most analytics work: running totals, ranking inside a group, and comparing a row to its neighbor (the LAG query above).

A running revenue total uses an aggregate inside a window. Note the nested SUM: the inner one groups by day, the outer one accumulates.

SELECT DATE(occurred_at) AS day,
       SUM(CAST(properties->>'$.order_total' AS DECIMAL(10,2))) AS revenue,
       SUM(SUM(CAST(properties->>'$.order_total' AS DECIMAL(10,2))))
         OVER (ORDER BY DATE(occurred_at)) AS running_revenue
FROM events
WHERE event_name = 'purchase_completed'
GROUP BY DATE(occurred_at)
ORDER BY day;

Ranking inside a group answers questions like “top three pages per day”. ROW_NUMBER gives each row a position, and the outer query filters on it.

WITH pv AS (
  SELECT DATE(occurred_at) AS day,
         SUBSTRING_INDEX(SUBSTRING(page_url, LOCATE('/', page_url, 9)), '?', 1) AS path
  FROM events
  WHERE event_name = 'page_view'
), daily_pages AS (
  SELECT day, path, COUNT(*) AS views FROM pv GROUP BY day, path
), ranked AS (
  SELECT day, path, views,
         ROW_NUMBER() OVER (PARTITION BY day ORDER BY views DESC, path) AS rn
  FROM daily_pages
)
SELECT day, path, views FROM ranked WHERE rn <= 3 ORDER BY day, rn;

Add a tie-breaker like path to the ORDER BY. Without it, ties return in arbitrary order, and two runs of the same report can disagree. Use RANK instead of ROW_NUMBER when you want ties to share a position. The CTE syntax used throughout this article is documented in the MySQL WITH reference, including recursive CTEs for generating date series when you need zero-filled days.

SQL Queries for User Analytics: Which Pattern Answers Which Question

How Real Systems Do This

Real products rarely run these queries raw against the event table at read time. Matomo, which stores data in MySQL or MariaDB, runs an archiving process that precomputes report tables so the interface does not scan raw logs. Plausible and PostHog both document ClickHouse as their analytical store, because a columnar engine scans date ranges and distinct counts far faster than a row store.

The common pattern is the same in all of them. Keep the raw events as the source of truth, compute daily aggregates once, and serve dashboards from the aggregates. You can copy that pattern with a nightly job that fills a daily_pageviews rollup. The next article builds a dashboard that shows numbers like these as KPI cards and a chart.

Decision Framework

1. What is the unit of the question: events, sessions or people? Pick the count that matches, and name it in the report title.
2. Does identity change over time? If users log in, build a person key before you count anything.
3. Which time zone and which date range semantics do readers expect? Fix both, and use half-open ranges.
4. Does the result depend on order? If yes, use conditional minimums or window functions, never plain counts.
5. How often will it run? If the answer is “on every dashboard load”, precompute it into a rollup.
6. Can you verify it on a hand-checkable dataset? If not, build one before you ship the query.

When NOT to Use This

• Funnels with many branches or strict multi-step time windows. The SQL becomes unreadable and slow. A purpose-built funnel engine, or a product analytics tool you buy, handles them better.
• Billions of events with interactive query needs. MySQL on a single node struggles with wide scans and distinct counts. Plan a move to a warehouse instead of tuning forever.
• Cross-device identity that needs probabilistic matching. Simple MAX(user_id) stitching is a floor, not a solution. If identity quality drives revenue decisions, buy or design a real identity graph.

Common Mistakes

• Counting COUNT(*) and calling it users, which inflates every metric by the average events per person.
• Using BETWEEN with an invented end time, which drops events in the last second of the day.
• Mixing the local time zone into DATE() on some queries and UTC on others, which makes yesterday’s DAU disagree between two dashboards.
• Using JSON values without a cast, which makes MAX and sorting compare text, so 9.5 beats 59.9.
• Reading immature cohorts as churn, which sends the team chasing a retention drop that is only missing data.
• Skipping the duplicate guard on event_id, which doubles revenue when a client retries.

Key Takeaways

• Resolve identity once in a view and count person_key everywhere.
• Store UTC, filter with >= and <, and convert zones only at the reporting edge.
• Count distinct people for DAU and MAU, and use a correlated subquery for rolling distinct counts in MySQL.
• Build funnels from per-person conditional minimums so order and repeats behave.
• Build retention from first-active day joined to later active days, and hide immature cohorts.
• Use window functions for running totals, rankings and sessionization, and add a tie-breaker to every ranking.
• Test each query on a dataset small enough to verify by hand.

FAQ

How do I calculate DAU and MAU in MySQL?

Group events by date and use COUNT(DISTINCT person_key) for DAU. For MAU, run a correlated subquery per day that counts distinct people in the trailing 30 days, because MySQL does not support DISTINCT inside window aggregates. Divide DAU by MAU to get stickiness.

How do I write a funnel query in MySQL?

Group by person and take the minimum timestamp of each step with MIN(CASE WHEN event_name = ... THEN occurred_at END). Count a person at a step only if that step’s time is at or after the previous step’s time. This respects order and counts each person once.

Does MySQL support window functions?

Yes. MySQL 8.0 added them, and MySQL 8.4 supports ROW_NUMBER, RANK, LAG, LEAD, FIRST_VALUE and aggregate functions with OVER. Check the reference for details such as frame clauses and unsupported combinations.

How do I calculate retention cohorts in SQL?

Find each person’s first active day as their cohort, join it to every day they were active, and compute the day difference. Count users per cohort and offset, then divide by the day 0 count with FIRST_VALUE.

Conclusion

Analytical SQL is mostly about choosing what to count and when to count it. Fix identity, time and duplicates first, and the DAU, funnel and retention queries become short and trustworthy. Next, turn these numbers into a dashboard.

Rule of thumb: count people, not rows, and prove every query on ten rows before you run it on ten million.

Last updated on 9 October 2026.