diff options
Diffstat (limited to 'abfragen.sql')
| -rw-r--r-- | abfragen.sql | 20 |
1 files changed, 20 insertions, 0 deletions
diff --git a/abfragen.sql b/abfragen.sql new file mode 100644 index 0000000..53ba62d --- /dev/null +++ b/abfragen.sql @@ -0,0 +1,20 @@ + +-- Ausdauernde + SELECT a.send_time - b.send_time AS "Dauer", date_trunc('day',a.send_time) + FROM ( SELECT MAX(send_time) AS send_time, from_ident + FROM omegle_messages + GROUP BY from_ident ) a + JOIN ( SELECT MIN(send_time) AS send_time, from_ident + FROM omegle_messages + GROUP BY from_ident ) b + ON a.from_ident = b.from_ident + WHERE a.send_time - b.send_time > interval '1 hour' +ORDER BY 1 DESC; + +-- Fleissigste + SELECT COUNT(*), from_ident, date_trunc('hour',MIN(send_time)) + FROM omegle_messages +GROUP BY from_ident + HAVING COUNT(*) > 10 +ORDER BY 1 DESC; + |