Advanced SQL & Functions

Window functions, arrays, lambdas, CTEs, and ClickHouse-specific SQL magic that makes complex analytics a breeze.

🪟 Window Functions

Window functions let you compute values across rows without collapsing them (unlike GROUP BY).

-- Running total of revenue by date
SELECT
    event_date,
    revenue,
    sum(revenue) OVER (ORDER BY event_date) AS running_total,
    row_number() OVER (PARTITION BY country ORDER BY revenue DESC) AS rank_in_country,
    lag(revenue) OVER (ORDER BY event_date) AS prev_day_revenue,
    revenue - lag(revenue) OVER (ORDER BY event_date) AS day_over_day_change
FROM daily_revenue;

🧒 ELI5: Window Functions

Imagine you're in a race. GROUP BY tells you "the average speed of all runners." A window function tells you "your speed AND how you compare to the runner before you AND your running total distance" — all in one row. You see the big picture without losing the details.

📦 Array Functions

ClickHouse has first-class array support — no need for unnest/lateral joins like in PostgreSQL.

-- Create and manipulate arrays
SELECT
    [1, 2, 3, 4, 5] AS arr,
    arrayMap(x -> x * 2, [1,2,3]) AS doubled,        -- [2, 4, 6]
    arrayFilter(x -> x > 2, [1,2,3,4]) AS filtered,  -- [3, 4]
    arraySum([10, 20, 30]) AS total,                  -- 60
    arrayJoin(['a', 'b', 'c']) AS exploded;            -- 3 rows

-- Real use case: User's browsing path analysis
SELECT
    user_id,
    groupArray(url) AS browsing_path,
    length(groupArray(url)) AS pages_visited,
    arrayElement(groupArray(url), 1) AS landing_page,
    arrayElement(groupArray(url), -1) AS exit_page
FROM events
GROUP BY user_id;

🔧 Lambda Functions

ClickHouse supports inline lambda functions for array processing — like Python's lambda:

-- Lambda syntax: x -> expression
SELECT
    arrayMap(x -> x * x, [1,2,3,4,5]) AS squares,
    -- [1, 4, 9, 16, 25]

    arrayFilter(x -> x % 2 = 0, [1,2,3,4,5,6]) AS evens,
    -- [2, 4, 6]

    arrayExists(x -> x > 100, [10,50,200]) AS has_big;
    -- 1 (true)

📝 CTEs (WITH Clause)

WITH
    daily_stats AS (
        SELECT
            event_date,
            count() AS events,
            uniq(user_id) AS users
        FROM events
        GROUP BY event_date
    ),
    averages AS (
        SELECT
            avg(events) AS avg_events,
            avg(users) AS avg_users
        FROM daily_stats
    )
SELECT
    d.*,
    round(d.events / a.avg_events * 100, 1) AS pct_of_avg
FROM daily_stats d, averages a
ORDER BY d.event_date DESC
LIMIT 30;

🎨 String & URL Functions

ClickHouse has built-in URL parsing — no regex needed!

SELECT
    domain('https://shop.example.com/products?id=123'),
    -- 'shop.example.com'

    path('https://shop.example.com/products?id=123'),
    -- '/products'

    extractURLParameter('https://shop.example.com/products?id=123&color=red', 'color'),
    -- 'red'

    topLevelDomain('https://shop.example.com');
    -- 'com'

💡 This is Huge for Web Analytics

Most databases require complex regex to parse URLs. ClickHouse does it natively, which is why it's the go-to for web analytics (it was literally built for Yandex.Metrica, a Google Analytics competitor).

📊 Conditional Aggregation

-- Count events by type in a single query
SELECT
    event_date,
    countIf(event_type = 'click') AS clicks,
    countIf(event_type = 'purchase') AS purchases,
    sumIf(revenue, event_type = 'purchase') AS total_revenue,
    round(countIf(event_type = 'purchase') / countIf(event_type = 'click') * 100, 2) AS conversion_rate
FROM events
GROUP BY event_date
ORDER BY event_date;

🧒 ELI5: countIf / sumIf

Instead of writing multiple queries or complex CASE WHEN statements, ClickHouse lets you say "count ONLY the rows where this condition is true." It's like asking a teacher: "How many students got an A?" vs "How many students are there total?" — in the same breath!

SQL Queries Performance

October 2026 practice

Checked against the ClickHouse documentation in October 2026. The JSON type has been production-ready since version 25.3. ClickHouse Cloud rewrites a MergeTree create onto the matching SharedMergeTree engine.

Window functions and array functions are ordinary SQL here. Approximate aggregates such as uniq are a choice when an exact distinct count is too expensive. Say which one you used. Higher-order array functions keep the work in one pass. Do not pull a thousand-row array into a dashboard if a GROUP BY would answer the question.

SELECT user_id, uniq(session_id) AS sessions
FROM events
GROUP BY user_id