add: graphs et rushs

1 files changed, 47 insertions, 0 deletions

diff --git a/graphs/sql/maas/req03.sql b/graphs/sql/maas/req03.sql
new file mode 100644
index 0000000..9b724cc
--- /dev/null
+++ b/graphs/sql/maas/req03.sql
@@ -0,0 +1,47 @@
+CREATE VIEW memorin.zoned_devices_logs AS
+    SELECT
+    device_serial,
+    version_id,
+    zone_id,
+    latitude AS lat,
+    longitude AS long,
+    temperature AS temp,
+    battery_percentage AS battery,
+    signal_strength
+    FROM
+    memorin.device_logs AS logs
+    WHERE
+    device_serial IN (
+        SELECT serial_number
+        FROM memorin.devices
+        WHERE deactivated_at IS NULL
+    )
+    AND version_id IN (
+        SELECT id
+        FROM memorin.device_versions
+        WHERE id = logs.version_id
+    )
+    AND temperature BETWEEN 34 AND 45
+    AND signal_strength BETWEEN 0 AND 5
+    AND battery_percentage BETWEEN 0 AND 100
+    AND longitude BETWEEN (
+        SELECT min_longitude
+        FROM memorin.geographic_zones
+        WHERE id = logs.zone_id
+    )
+    AND (
+        SELECT max_longitude
+        FROM memorin.geographic_zones
+        WHERE id = logs.zone_id
+    )
+    AND latitude BETWEEN (
+        SELECT min_latitude
+        FROM memorin.geographic_zones
+        WHERE id = logs.zone_id
+    )
+    AND (
+        SELECT max_latitude
+        FROM memorin.geographic_zones
+        WHERE id = logs.zone_id
+    )
+    WITH CHECK OPTION;