ClickHouse Database Schema

Database: syn

All benchmark materialized views reside in the syn database.

Materialized View 1: mv_restaurant_monthly

Restaurant-level monthly aggregations with city, country, and tag references.

CREATE MATERIALIZED VIEW syn.mv_restaurant_monthly
REFRESH EVERY 1 DAY
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (restaurant_id, period_date)
SETTINGS allow_nullable_key = 1
AS 
SELECT
    r.restaurant_id AS restaurant_id,
    any(rest.city_id) AS city_id,
    any(rest.country_id) AS country_id,
    arrayFilter(x -> isNotNull(x), groupArray(DISTINCT rtr.restaurant_tag_id)) AS tag_ids,
    toStartOfMonth(r.date) AS period_date,
    toYear(r.date) AS year,
    toMonth(r.date) AS month_number,
    formatDateTime(r.date, '%Y-%m') AS month_label,
    ROUND(SUM(rp.price_cents / 100), 0) AS gmv,
    COUNT(DISTINCT r.id) AS reservations_count,
    SUM(r.party_size) AS total_covers,
    if(
        SUM(r.party_size) > 0,
        ROUND(SUM(rp.price_cents / 100) / SUM(r.party_size), 2),
        0
    ) AS avg_spend_per_person,
    max(now()) AS updated_at
FROM booking_production.reservations AS r
INNER JOIN booking_production.reservation_properties AS rp
    ON r.id = rp.reservation_id
INNER JOIN booking_production.restaurants AS rest
    ON r.restaurant_id = rest.id
LEFT JOIN booking_production.restaurant_tags_restaurants AS rtr
    ON rest.id = rtr.restaurant_id
WHERE
    r.date >= addMonths(toStartOfMonth(today()), -12)
    AND r.active = 1
    AND r.ack = 1
    AND rp.revenue > 0
GROUP BY
    r.restaurant_id,
    toStartOfMonth(r.date),
    toYear(r.date),
    toMonth(r.date),
    formatDateTime(r.date, '%Y-%m');

Column Descriptions

Materialized View 2: mv_restaurant_weekly

Restaurant-level weekly aggregations using ISO week numbering.

CREATE MATERIALIZED VIEW syn.mv_restaurant_weekly
REFRESH EVERY 1 DAY
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (restaurant_id, period_date)
SETTINGS allow_nullable_key = 1
AS
SELECT
    r.restaurant_id AS restaurant_id,
    any(rest.city_id) AS city_id,
    any(rest.country_id) AS country_id,
    arrayFilter(x -> isNotNull(x), groupArray(DISTINCT rtr.restaurant_tag_id)) AS tag_ids,
    toMonday(r.date) AS period_date,
    toISOYear(r.date) AS year,
    toISOWeek(r.date) AS week_number,
    formatDateTime(toMonday(r.date), '%b %d') || ' - ' ||
        formatDateTime(toMonday(r.date) + INTERVAL 6 DAY, '%b %d') AS week_label,
    toMonday(r.date) AS date_range_start,
    toMonday(r.date) + INTERVAL 6 DAY AS date_range_end,
    ROUND(SUM(rp.price_cents / 100), 0) AS gmv,
    COUNT(DISTINCT r.id) AS reservations_count,
    SUM(r.party_size) AS total_covers,
    if(
        SUM(r.party_size) > 0,
        ROUND(SUM(rp.price_cents / 100) / SUM(r.party_size), 2),
        0
    ) AS avg_spend_per_person,
    max(now()) AS updated_at
FROM booking_production.reservations r
INNER JOIN booking_production.reservation_properties rp
    ON r.id = rp.reservation_id
INNER JOIN booking_production.restaurants rest
    ON r.restaurant_id = rest.id
LEFT JOIN booking_production.restaurant_tags_restaurants rtr
    ON rest.id = rtr.restaurant_id
WHERE
    toMonday(r.date) >= toMonday(addMonths(toStartOfMonth(today()), -12))
    AND r.active = 1
    AND r.ack = 1
    AND rp.revenue > 0
GROUP BY
    r.restaurant_id,
    toMonday(r.date),
    toISOYear(r.date),
    toISOWeek(r.date),
    formatDateTime(toMonday(r.date), '%b %d') || ' - ' || 
        formatDateTime(toMonday(r.date) + INTERVAL 6 DAY, '%b %d'),
    toMonday(r.date),
    toMonday(r.date) + INTERVAL 6 DAY;

Column Descriptions

Key Design Decisions

1. Single MV Architecture

We use only 2 MVs instead of 6 because:

• City and tag benchmarks are aggregated on-the-fly from restaurant data
• Reduces storage requirements
• Ensures data consistency
• Simplifies refresh strategy

2. tag_ids as Array

Tags are stored as Array(UInt64) to:

• Support multiple tags per restaurant
• Enable efficient filtering with ARRAY JOIN
• Avoid separate join tables

3. ISO Week Numbering

Weekly views use ISO week numbering:

• toISOYear() for year (handles year boundaries)
• toISOWeek() for week number (1-53)
• Weeks start on Monday

4. Automatic Refresh

MVs use REFRESH EVERY 1 DAY:

• No external scheduler needed
• ClickHouse handles refresh automatically
• Data freshness within 24 hours

Example Queries

Restaurant Metrics for a Period

SELECT *
FROM syn.mv_restaurant_monthly
WHERE restaurant_id = 1198
  AND year = 2025
  AND month_number = 9;

City Aggregation

SELECT
    city_id,
    month_label,
    ROUND(SUM(gmv), 0) AS total_city_gmv,
    COUNT(DISTINCT restaurant_id) AS total_restaurants,
    ROUND(SUM(gmv) / COUNT(DISTINCT restaurant_id), 2) AS avg_gmv_per_restaurant
FROM syn.mv_restaurant_monthly
WHERE city_id = 1
  AND year = 2025
GROUP BY city_id, month_label
ORDER BY month_label ASC;

Tag Aggregation with ARRAY JOIN

SELECT
    tag_id,
    month_label,
    ROUND(SUM(gmv), 0) AS total_tag_gmv,
    COUNT(DISTINCT restaurant_id) AS restaurants_count,
    ROUND(SUM(gmv) / COUNT(DISTINCT restaurant_id), 2) AS avg_gmv_per_restaurant
FROM syn.mv_restaurant_monthly
ARRAY JOIN tag_ids AS tag_id
WHERE tag_id = 7  -- Chinese cuisine
  AND country_id = 218  -- Thailand
  AND year = 2025
GROUP BY tag_id, month_label
ORDER BY month_label ASC;