12 SQL Queries Every Web Analytics Dashboard Needs

Every query below runs on DuckDB against an events table with site_id, timestamp, visitor_id, session_id, path, referrer_host, country, device_type, and event_name. Adjust names to your schema; the shapes transfer.

1. Headline KPIs

SELECT count(*)                        AS pageviews,
       count(DISTINCT session_id)      AS sessions,
       count(DISTINCT visitor_id)      AS visitors,
       round(count(*)::DOUBLE / nullif(count(DISTINCT session_id), 0), 2)
                                       AS pages_per_session
FROM events
WHERE site_id = ? AND timestamp >= ? AND timestamp < ?;

Use half-open ranges (>= start AND < end). BETWEEN on timestamps includes the end boundary and double-counts the instant where two periods meet.

2. Daily trend with gap filling

WITH days AS (
  SELECT unnest(generate_series(?::DATE, ?::DATE, INTERVAL 1 DAY))::DATE AS day
)
SELECT d.day,
       count(e.session_id)             AS pageviews,
       count(DISTINCT e.visitor_id)    AS visitors
FROM days d
LEFT JOIN events e
       ON e.timestamp::DATE = d.day AND e.site_id = ?
GROUP BY d.day
ORDER BY d.day;

The generated date series is what stops a zero-traffic day from vanishing and making your chart lie about the shape of the trend.

3. Top pages

SELECT path,
       count(*)                     AS pageviews,
       count(DISTINCT visitor_id)   AS unique_visitors
FROM events
WHERE site_id = ? AND timestamp >= ? AND event_name = 'pageview'
GROUP BY path
ORDER BY pageviews DESC
LIMIT 25;

4. Top referrers, excluding self

SELECT coalesce(nullif(referrer_host, ''), '(direct)') AS source,
       count(DISTINCT session_id) AS sessions
FROM events
WHERE site_id = ? AND timestamp >= ?
  AND (referrer_host IS NULL OR referrer_host NOT LIKE '%yourdomain.com')
GROUP BY 1
ORDER BY sessions DESC
LIMIT 20;

Filtering your own domain is essential — internal navigation otherwise dominates the referrer report and hides every real source.

5. Sessions, bounce rate, and duration

WITH s AS (
  SELECT session_id,
         min(timestamp) AS started_at,
         max(timestamp) AS ended_at,
         count(*)       AS depth
  FROM events
  WHERE site_id = ? AND timestamp >= ?
  GROUP BY session_id
)
SELECT count(*)                                        AS sessions,
       round(100.0 * avg(CASE WHEN depth = 1 THEN 1.0 ELSE 0 END), 1) AS bounce_pct,
       round(median(epoch(ended_at - started_at)), 0)  AS median_seconds
FROM s;

Median, not mean. One tab left open overnight destroys an average session duration.

6. Entry pages

SELECT path, count(*) AS entries
FROM (
  SELECT session_id, path,
         row_number() OVER (PARTITION BY session_id ORDER BY timestamp) AS rn
  FROM events WHERE site_id = ? AND timestamp >= ?
)
WHERE rn = 1
GROUP BY path
ORDER BY entries DESC
LIMIT 20;

7. Exit pages with exit rate

WITH ranked AS (
  SELECT session_id, path, timestamp,
         row_number() OVER (PARTITION BY session_id ORDER BY timestamp DESC) AS rn_desc
  FROM events WHERE site_id = ? AND timestamp >= ?
)
SELECT path,
       count(*) FILTER (WHERE rn_desc = 1) AS exits,
       count(*)                            AS views,
       round(100.0 * count(*) FILTER (WHERE rn_desc = 1) / count(*), 1) AS exit_rate_pct
FROM ranked
GROUP BY path
HAVING count(*) > 50
ORDER BY exits DESC
LIMIT 20;

Exit rate without the view count is a trap — a page viewed twice with two exits is 100% and means nothing. Hence the HAVING.

8. New vs returning visitors

WITH firsts AS (
  SELECT visitor_id, min(timestamp)::DATE AS first_day
  FROM events WHERE site_id = ? GROUP BY visitor_id
)
SELECT CASE WHEN f.first_day >= current_date - 0 THEN 'new' ELSE 'returning' END AS type,
       count(DISTINCT e.visitor_id) AS visitors
FROM events e JOIN firsts f USING (visitor_id)
WHERE e.site_id = ? AND e.timestamp::DATE = current_date
GROUP BY 1;

With rotating-salt visitor IDs this is bounded by the salt window — worth stating on the chart rather than letting people over-read it.

9. Geography

SELECT country,
       count(DISTINCT session_id) AS sessions,
       round(100.0 * count(DISTINCT session_id)
             / sum(count(DISTINCT session_id)) OVER (), 1) AS share_pct
FROM events
WHERE site_id = ? AND timestamp >= ? AND country IS NOT NULL
GROUP BY country
ORDER BY sessions DESC;

The empty OVER () window computes the grand total alongside each row — one pass, no self-join.

10. Period-over-period growth

WITH periods AS (
  SELECT CASE WHEN timestamp >= current_date - 7  THEN 'current'
              WHEN timestamp >= current_date - 14 THEN 'previous' END AS period,
         session_id, visitor_id
  FROM events
  WHERE site_id = ? AND timestamp >= current_date - 14
)
SELECT
  count(DISTINCT visitor_id) FILTER (WHERE period = 'current')  AS current_visitors,
  count(DISTINCT visitor_id) FILTER (WHERE period = 'previous') AS previous_visitors,
  round(100.0 * (count(DISTINCT visitor_id) FILTER (WHERE period = 'current')
               - count(DISTINCT visitor_id) FILTER (WHERE period = 'previous'))
        / nullif(count(DISTINCT visitor_id) FILTER (WHERE period = 'previous'), 0), 1)
        AS change_pct
FROM periods;

11. Pages growing fastest

SELECT path,
       count(*) FILTER (WHERE timestamp >= current_date - 7)  AS this_week,
       count(*) FILTER (WHERE timestamp >= current_date - 14
                          AND timestamp <  current_date - 7)  AS last_week,
       round(100.0 * (count(*) FILTER (WHERE timestamp >= current_date - 7)
                    - count(*) FILTER (WHERE timestamp >= current_date - 14
                                         AND timestamp < current_date - 7))
             / nullif(count(*) FILTER (WHERE timestamp >= current_date - 14
                                         AND timestamp < current_date - 7), 0), 1)
             AS growth_pct
FROM events
WHERE site_id = ? AND timestamp >= current_date - 14
GROUP BY path
HAVING last_week > 20
ORDER BY growth_pct DESC
LIMIT 20;

The HAVING last_week > 20 filter is what keeps this list useful. Without it, every page that went from 1 view to 4 shows +300% and buries the real movers.

12. Traffic anomaly detection

WITH daily AS (
  SELECT timestamp::DATE AS day, count(*) AS views
  FROM events WHERE site_id = ? AND timestamp >= current_date - 60
  GROUP BY 1
),
stats AS (
  SELECT day, views,
         avg(views)    OVER w AS ma,
         stddev(views) OVER w AS sd
  FROM daily
  WINDOW w AS (ORDER BY day ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING)
)
SELECT day, views, round(ma, 0) AS baseline,
       round((views - ma) / nullif(sd, 0), 2) AS z_score
FROM stats
WHERE abs((views - ma) / nullif(sd, 0)) > 2.5
ORDER BY day DESC;

The window frame ends at 1 PRECEDING deliberately — including today in its own baseline dampens exactly the spike you are trying to detect.

Frequently asked questions

Why do trend queries need gap filling?

Because days with no traffic produce no rows, and a chart drawn from that data connects across the gap as though the missing days did not exist. Joining against a generated date series makes zero days explicit.

How do I exclude internal referrers from a referrer report?

Filter out your own hostname. Without it, internal navigation dominates the report and your actual acquisition sources are buried beneath your own pages.

What is the difference between exit rate and bounce rate?

Bounce rate is the share of sessions that ended after a single page. Exit rate is the share of views of a given page that were the last in their session — a page can have a high exit rate and no bounces at all.

Should these queries run against raw events or rollups?

Raw events while volume is small and flexibility matters. Once dashboards repeat the same aggregations over long ranges, precompute daily rollups and query those — it is the single biggest dashboard speedup available.

Will these queries work outside DuckDB?

Mostly. They use standard SQL with window functions, so PostgreSQL and ClickHouse need only minor dialect changes — date functions and generated series are the usual edits.

A note on running these safely

If you expose SQL to users, run it on a read-only connection, enforce a statement timeout and a row limit, and parameterize every value. Never build these strings by interpolation — not even for internal IDs, which have a way of becoming user input later.

InsightsTrack's SQL Editor runs queries against a read-only DuckDB connection with those guards in place, so you can run all twelve against your own data without building a query service first. For deeper patterns see window functions, cohort retention, and DuckDB tuning.