-- Migrated from DuckDB test: test/sql/join/ performance pattern tests
-- Tests join patterns common in time-series queries
CREATE TABLE metrics_perf(metric_id INTEGER, metric_name VARCHAR, "value" DOUBLE, timestamp_val BIGINT, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

CREATE TABLE metadata_perf(metric_id INTEGER, unit VARCHAR, description VARCHAR, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

CREATE TABLE thresholds_perf(metric_id INTEGER, warning_level DOUBLE, critical_level DOUBLE, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO metrics_perf VALUES
(1, 'cpu_usage', 65.2, 1700000000, 1000), (1, 'cpu_usage', 72.1, 1700000060, 2000),
(2, 'memory_usage', 85.5, 1700000000, 3000), (2, 'memory_usage', 78.3, 1700000060, 4000),
(3, 'disk_io', 120.7, 1700000000, 5000), (3, 'disk_io', 95.2, 1700000060, 6000);

Affected Rows: 6

INSERT INTO metadata_perf VALUES
(1, 'percent', 'CPU utilization percentage', 1000),
(2, 'percent', 'Memory utilization percentage', 2000),
(3, 'MB/s', 'Disk I/O throughput', 3000);

Affected Rows: 3

INSERT INTO thresholds_perf VALUES
(1, 70.0, 90.0, 1000), (2, 80.0, 95.0, 2000), (3, 100.0, 150.0, 3000);

Affected Rows: 3

-- Join for monitoring dashboard
SELECT
  m.metric_name,
  md.unit,
  m."value",
  t.warning_level,
  t.critical_level,
  CASE
    WHEN m."value" >= t.critical_level THEN 'CRITICAL'
    WHEN m."value" >= t.warning_level THEN 'WARNING'
    ELSE 'OK'
  END as status
FROM metrics_perf m
INNER JOIN metadata_perf md ON m.metric_id = md.metric_id
INNER JOIN thresholds_perf t ON m.metric_id = t.metric_id
ORDER BY m.timestamp_val, m.metric_id;

+--------------+---------+-------+---------------+----------------+---------+
| metric_name  | unit    | value | warning_level | critical_level | status  |
+--------------+---------+-------+---------------+----------------+---------+
| cpu_usage    | percent | 65.2  | 70.0          | 90.0           | OK      |
| memory_usage | percent | 85.5  | 80.0          | 95.0           | WARNING |
| disk_io      | MB/s    | 120.7 | 100.0         | 150.0          | WARNING |
| cpu_usage    | percent | 72.1  | 70.0          | 90.0           | WARNING |
| memory_usage | percent | 78.3  | 80.0          | 95.0           | OK      |
| disk_io      | MB/s    | 95.2  | 100.0         | 150.0          | OK      |
+--------------+---------+-------+---------------+----------------+---------+

-- Time-series join with latest values
SELECT
  latest_metrics.metric_name,
  latest_metrics.latest_value,
  md.unit,
  t.warning_level
FROM (
  SELECT
    metric_id,
    metric_name,
    "value" as latest_value,
    ROW_NUMBER() OVER (PARTITION BY metric_id ORDER BY timestamp_val DESC) as rn
  FROM metrics_perf
) latest_metrics
INNER JOIN metadata_perf md ON latest_metrics.metric_id = md.metric_id
INNER JOIN thresholds_perf t ON latest_metrics.metric_id = t.metric_id
WHERE latest_metrics.rn = 1
ORDER BY latest_metrics.metric_id;

+--------------+--------------+---------+---------------+
| metric_name  | latest_value | unit    | warning_level |
+--------------+--------------+---------+---------------+
| cpu_usage    | 72.1         | percent | 70.0          |
| memory_usage | 78.3         | percent | 80.0          |
| disk_io      | 95.2         | MB/s    | 100.0         |
+--------------+--------------+---------+---------------+

-- Historical analysis join
SELECT
  md.description,
  COUNT(*) as total_readings,
  AVG(m."value") as avg_value,
  COUNT(CASE WHEN m."value" > t.warning_level THEN 1 END) as warning_count,
  COUNT(CASE WHEN m."value" > t.critical_level THEN 1 END) as critical_count
FROM metrics_perf m
INNER JOIN metadata_perf md ON m.metric_id = md.metric_id
INNER JOIN thresholds_perf t ON m.metric_id = t.metric_id
GROUP BY md.description, m.metric_id
ORDER BY critical_count DESC, warning_count DESC, avg_value DESC;

+-------------------------------+----------------+-----------+---------------+----------------+
| description                   | total_readings | avg_value | warning_count | critical_count |
+-------------------------------+----------------+-----------+---------------+----------------+
| Disk I/O throughput           | 2              | 107.95    | 1             | 0              |
| Memory utilization percentage | 2              | 81.9      | 1             | 0              |
| CPU utilization percentage    | 2              | 68.65     | 1             | 0              |
+-------------------------------+----------------+-----------+---------------+----------------+

DROP TABLE metrics_perf;

Affected Rows: 0

DROP TABLE metadata_perf;

Affected Rows: 0

DROP TABLE thresholds_perf;

Affected Rows: 0