graphs/sql/maas/req02.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

CREATE TEMPORARY VIEW devices_metrics AS (
    SELECT
    device_serial,
    version_id,
    logs.id AS log_id,
    latitude AS lat,
    longitude AS long,
    temperature AS temp,
    battery_percentage AS battery,
    signal_strength
    FROM
    (
        SELECT serial_number
        FROM memorin.devices
        WHERE deactivated_at IS NULL
    ) AS active,
    memorin.device_logs AS logs
    INNER JOIN memorin.device_versions AS v
    ON version_id = v.id
    WHERE serial_number = device_serial
    ORDER BY device_serial, v.released_at DESC, log_id
);