-- Migrated from DuckDB test: test/sql/join/ join with aggregate tests
-- Tests joins combined with aggregate functions
CREATE TABLE sensors(sensor_id INTEGER, sensor_name VARCHAR, "location" VARCHAR, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

CREATE TABLE readings(reading_id INTEGER, sensor_id INTEGER, "value" DOUBLE, reading_time TIMESTAMP, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO sensors VALUES
(1, 'TempSensor1', 'Room A', 1000), (2, 'TempSensor2', 'Room B', 2000),
(3, 'HumiditySensor1', 'Room A', 3000), (4, 'HumiditySensor2', 'Room B', 4000);

Affected Rows: 4

INSERT INTO readings VALUES
(1, 1, 22.5, '2023-01-01 10:00:00', 1000), (2, 1, 23.1, '2023-01-01 11:00:00', 2000),
(3, 1, 21.8, '2023-01-01 12:00:00', 3000), (4, 2, 25.3, '2023-01-01 10:00:00', 4000),
(5, 2, 26.0, '2023-01-01 11:00:00', 5000), (6, 2, 24.7, '2023-01-01 12:00:00', 6000),
(7, 3, 45.2, '2023-01-01 10:00:00', 7000), (8, 3, 46.8, '2023-01-01 11:00:00', 8000),
(9, 4, 52.1, '2023-01-01 10:00:00', 9000), (10, 4, 51.3, '2023-01-01 11:00:00', 10000);

Affected Rows: 10

-- Join with basic aggregation
SELECT
  s.sensor_name, s."location",
  COUNT(r.reading_id) as reading_count,
  AVG(r."value") as avg_value,
  MIN(r."value") as min_value,
  MAX(r."value") as max_value
FROM sensors s
INNER JOIN readings r ON s.sensor_id = r.sensor_id
GROUP BY s.sensor_id, s.sensor_name, s."location"
ORDER BY s.sensor_name;

+-----------------+----------+---------------+--------------------+-----------+-----------+
| sensor_name     | location | reading_count | avg_value          | min_value | max_value |
+-----------------+----------+---------------+--------------------+-----------+-----------+
| HumiditySensor1 | Room A   | 2             | 46.0               | 45.2      | 46.8      |
| HumiditySensor2 | Room B   | 2             | 51.7               | 51.3      | 52.1      |
| TempSensor1     | Room A   | 3             | 22.46666666666667  | 21.8      | 23.1      |
| TempSensor2     | Room B   | 3             | 25.333333333333332 | 24.7      | 26.0      |
+-----------------+----------+---------------+--------------------+-----------+-----------+

-- Join with time-based aggregation
SELECT
  s."location",
  DATE_TRUNC('hour', r.reading_time) as hour_bucket,
  COUNT(*) as readings_per_hour,
  AVG(r."value") as avg_hourly_value
FROM sensors s
INNER JOIN readings r ON s.sensor_id = r.sensor_id
GROUP BY s."location", DATE_TRUNC('hour', r.reading_time)
ORDER BY s."location", hour_bucket;

+----------+---------------------+-------------------+------------------+
| location | hour_bucket         | readings_per_hour | avg_hourly_value |
+----------+---------------------+-------------------+------------------+
| Room A   | 2023-01-01T10:00:00 | 2                 | 33.85            |
| Room A   | 2023-01-01T11:00:00 | 2                 | 34.95            |
| Room A   | 2023-01-01T12:00:00 | 1                 | 21.8             |
| Room B   | 2023-01-01T10:00:00 | 2                 | 38.7             |
| Room B   | 2023-01-01T11:00:00 | 2                 | 38.65            |
| Room B   | 2023-01-01T12:00:00 | 1                 | 24.7             |
+----------+---------------------+-------------------+------------------+

-- Aggregation before join
SELECT
  s.sensor_name, s."location", agg_readings.avg_value, agg_readings.reading_count
FROM sensors s
INNER JOIN (
  SELECT sensor_id, AVG("value") as avg_value, COUNT(*) as reading_count
  FROM readings
  GROUP BY sensor_id
) agg_readings ON s.sensor_id = agg_readings.sensor_id
WHERE agg_readings.avg_value > 30.0
ORDER BY agg_readings.avg_value DESC;

+-----------------+----------+-----------+---------------+
| sensor_name     | location | avg_value | reading_count |
+-----------------+----------+-----------+---------------+
| HumiditySensor2 | Room B   | 51.7      | 2             |
| HumiditySensor1 | Room A   | 46.0      | 2             |
+-----------------+----------+-----------+---------------+

-- Multiple aggregation levels with joins
SELECT
  location_summary.location,
  location_summary.sensor_count,
  location_summary.avg_readings_per_sensor,
  location_summary.location_avg_value
FROM (
  SELECT
    s."location",
    COUNT(DISTINCT s.sensor_id) as sensor_count,
    COUNT(r.reading_id) / COUNT(DISTINCT s.sensor_id) as avg_readings_per_sensor,
    ROUND(AVG(r."value"), 6) as location_avg_value
  FROM sensors s
  INNER JOIN readings r ON s.sensor_id = r.sensor_id
  GROUP BY s."location"
) location_summary
ORDER BY location_summary.location_avg_value DESC;

+----------+--------------+-------------------------+--------------------+
| location | sensor_count | avg_readings_per_sensor | location_avg_value |
+----------+--------------+-------------------------+--------------------+
| Room B   | 2            | 2                       | 35.88              |
| Room A   | 2            | 2                       | 31.88              |
+----------+--------------+-------------------------+--------------------+

-- Join with aggregated conditions
SELECT
  s.sensor_name,
  high_readings.high_count,
  high_readings.avg_high_value
FROM sensors s
INNER JOIN (
  SELECT
    sensor_id,
    COUNT(*) as high_count,
    AVG("value") as avg_high_value
  FROM readings
  WHERE "value" > 25.0
  GROUP BY sensor_id
  HAVING COUNT(*) >= 2
) high_readings ON s.sensor_id = high_readings.sensor_id
ORDER BY high_readings.avg_high_value DESC;

+-----------------+------------+----------------+
| sensor_name     | high_count | avg_high_value |
+-----------------+------------+----------------+
| HumiditySensor2 | 2          | 51.7           |
| HumiditySensor1 | 2          | 46.0           |
| TempSensor2     | 2          | 25.65          |
+-----------------+------------+----------------+

DROP TABLE sensors;

Affected Rows: 0

DROP TABLE readings;

Affected Rows: 0