Comprehensive comparison for Database technology in Software Development applications

-- ClickHouse Schema and Queries for Application Event Tracking System
-- This example demonstrates a production-ready event tracking database for software applications

-- Create database for application analytics
CREATE DATABASE IF NOT EXISTS app_analytics;

USE app_analytics;

-- Main events table using MergeTree engine with partitioning
CREATE TABLE IF NOT EXISTS events (
    event_id UUID DEFAULT generateUUIDv4(),
    event_timestamp DateTime64(3) DEFAULT now64(),
    event_date Date DEFAULT toDate(event_timestamp),
    user_id String,
    session_id String,
    event_type LowCardinality(String),
    event_name String,
    app_version String,
    platform LowCardinality(String),
    country_code LowCardinality(String),
    properties String, -- JSON string for flexible event properties
    error_code Nullable(String),
    error_message Nullable(String),
    duration_ms UInt32 DEFAULT 0,
    INDEX idx_user_id user_id TYPE bloom_filter GRANULARITY 4,
    INDEX idx_session_id session_id TYPE bloom_filter GRANULARITY 4
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, event_type, event_timestamp)
TTL event_date + INTERVAL 90 DAY
SETTINGS index_granularity = 8192;

-- Materialized view for daily active users aggregation
CREATE MATERIALIZED VIEW IF NOT EXISTS daily_active_users_mv
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, platform, country_code)
AS SELECT
    event_date,
    platform,
    country_code,
    uniqState(user_id) AS unique_users
FROM events
GROUP BY event_date, platform, country_code;

-- Materialized view for error tracking
CREATE MATERIALIZED VIEW IF NOT EXISTS error_summary_mv
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, error_code, platform)
AS SELECT
    event_date,
    error_code,
    platform,
    app_version,
    count() AS error_count,
    uniqState(user_id) AS affected_users
FROM events
WHERE error_code IS NOT NULL
GROUP BY event_date, error_code, platform, app_version;

-- Query: Insert sample events with error handling
INSERT INTO events (user_id, session_id, event_type, event_name, platform, country_code, properties, duration_ms)
VALUES 
    ('user_12345', 'session_abc', 'page_view', 'home_page', 'web', 'US', '{"referrer":"google"}', 250),
    ('user_67890', 'session_def', 'button_click', 'checkout', 'mobile', 'UK', '{"product_id":"prod_123"}', 150),
    ('user_12345', 'session_abc', 'api_call', 'get_products', 'web', 'US', '{"endpoint":"/api/v1/products"}', 450);

-- Query: Get daily active users by platform for last 7 days
SELECT 
    event_date,
    platform,
    uniqMerge(unique_users) AS dau
FROM daily_active_users_mv
WHERE event_date >= today() - INTERVAL 7 DAY
GROUP BY event_date, platform
ORDER BY event_date DESC, platform;

-- Query: Analyze user session funnel with conversion rates
WITH funnel_events AS (
    SELECT
        session_id,
        user_id,
        countIf(event_name = 'home_page') AS step1_home,
        countIf(event_name = 'product_view') AS step2_product,
        countIf(event_name = 'add_to_cart') AS step3_cart,
        countIf(event_name = 'checkout') AS step4_checkout
    FROM events
    WHERE event_date >= today() - INTERVAL 1 DAY
    GROUP BY session_id, user_id
)
SELECT
    countIf(step1_home > 0) AS users_step1,
    countIf(step2_product > 0) AS users_step2,
    countIf(step3_cart > 0) AS users_step3,
    countIf(step4_checkout > 0) AS users_step4,
    round(countIf(step2_product > 0) / countIf(step1_home > 0) * 100, 2) AS conversion_1_to_2,
    round(countIf(step3_cart > 0) / countIf(step2_product > 0) * 100, 2) AS conversion_2_to_3,
    round(countIf(step4_checkout > 0) / countIf(step3_cart > 0) * 100, 2) AS conversion_3_to_4
FROM funnel_events;

-- Query: Get top errors by frequency with affected user count
SELECT
    error_code,
    platform,
    app_version,
    sum(error_count) AS total_errors,
    uniqMerge(affected_users) AS unique_affected_users,
    round(total_errors / unique_affected_users, 2) AS avg_errors_per_user
FROM error_summary_mv
WHERE event_date >= today() - INTERVAL 7 DAY
GROUP BY error_code, platform, app_version
ORDER BY total_errors DESC
LIMIT 10;

-- Query: Calculate p95 latency by event type for performance monitoring
SELECT
    event_type,
    event_name,
    count() AS event_count,
    round(avg(duration_ms), 2) AS avg_duration_ms,
    quantile(0.95)(duration_ms) AS p95_duration_ms,
    quantile(0.99)(duration_ms) AS p99_duration_ms
FROM events
WHERE event_date >= today() - INTERVAL 1 DAY
    AND duration_ms > 0
GROUP BY event_type, event_name
HAVING event_count > 100
ORDER BY p95_duration_ms DESC;