abfragen.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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;