I have a database where there is an table wm for water meters :
+------+---------------+
| Code | name |
+------+---------------+
| wm1 | water meter 1 |
| wm2 | water meter 2 |
| wm3 | water meter 3 |
+------+---------------+
and another table counters where there are counters value :
+------+---------+-------+------------+
| Code | Code_wm | value | created_at |
+------+---------+-------+------------+
| 1 | wm1 | 100 | 2020-10-18 |
| 2 | wm1 | 0 | 2020-10-19 |
| 3 | wm2 | 0 | 2020-10-18 |
| 4 | wm2 | 100 | 2020-10-19 |
| 5 | wm3 | 0 | 2020-10-18 |
| 6 | wm3 | 100 | 2020-10-19 |
+------+---------+-------+------------+
i want get this result :
| code_wm | result | Date |
+---------+--------+------------+
| wm1 | 0-100 | 2020-10-19 |
| wm2 | 100-0 | 2020-10-19 |
| wm3 | 100-0 | 2020-10-19 |
+---------+--------+------------+
but when i try :
SELECT code_wm , LAG(value,1,0) OVER ( ORDER BY code_wm) as result
FROM counters
i don't get the correct result : https://www.db-fiddle.com/f/7TuSTaukG336tqnTNDg4em/0
