1 files changed, 69 insertions, 5 deletions

diff --git a/abfragen.sql b/abfragen.sql
index 53ba62d..557bcb0 100644
--- a/abfragen.sql
+++ b/abfragen.sql
@@ -1,5 +1,4 @@
-
--- Ausdauernde
+-- Dauer der längsten Gespräche
   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 
@@ -9,12 +8,77 @@
          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;
+ORDER BY 1 DESC
+LIMIT 10;
 
--- Fleissigste
+-- Poster mit den meisten abgesendeten Nachrichten
   SELECT COUNT(*), from_ident, date_trunc('hour',MIN(send_time))
     FROM omegle_messages 
 GROUP BY from_ident 
-  HAVING COUNT(*) > 10 
+  HAVING COUNT(*) > 200
 ORDER BY 1 DESC;
 
+
+-- Anteil von GEsprächen in denen ein Begriff vorkommt
+SELECT to_char(a.tag,'DD.MM FMDay'), (b.count/CAST(a.count as real))*100 AS Anteil
+FROM (
+SELECT date_trunc('day', om1.send_time) AS Tag, COUNT(*)
+FROM omegle_messages om1
+GROUP BY date_trunc('day', om1.send_time)
+) a JOIN (
+SELECT date_trunc('day', om1.send_time) AS Tag, COUNT(*)
+FROM omegle_messages om1
+WHERE from_ident IN ( SELECT DISTINCT from_ident
+  FROM omegle_messages
+  WHERE message ILIKE '%hell%')
+GROUP BY date_trunc('day', om1.send_time)
+) b ON a.Tag = b.Tag;
+
+
+
+-- Gesprächsfetzen   - DEFEKT
+SELECT om1.message AS msgA,
+       om2.message AS msgB
+  FROM omegle_messages om1,
+       omegle_messages om2
+ WHERE om1.from_ident = om2.to_ident
+       AND om2.send_time > om1.send_time
+       AND om2.send_time = ( SELECT MAX(send_time) from omegle_messages om3 WHERE om3.send_time > om1.send_time AND om1.from_ident = om3.to_ident );
+       AND LENGTH(om1.message) < 100 AND LENGTH(om2.message) < 100;
+ LIMIT 10
+;
+
+
+
+-- ALle möglichen Partner
+
+
+SELECT to_char(MIN(om1.send_time),'DD') || '_' || om1.from_ident
+FROM omegle_messages om1
+GROUP BY om1.from_ident
+LIMIT 4
+;
+
+SELECT DISTINCT om1.to_ident, om2.from_ident
+FROM omegle_messages om1 JOIN (
+     SELECT DISTINCT from_ident
+       FROM omegle_messages
+) om2 ON om1.from_ident = om2.from_ident
+LIMIT 10;
+
+
+
+SELECT initiator,MAX(send_time)
+FROM (
+     SELECT SUBSTR(from_ident||to_ident,0,7) AS "initiator", MAX(send_time) AS "send_time"
+     FROM omegle_messages om1
+     WHERE from_ident IS NOT NULL and to_ident IS NOT NULL
+     GROUP BY from_ident || to_ident
+     UNION
+     SELECT SUBSTR(to_ident||from_ident,7,12) AS "initiator", MAX(send_time) AS "send_time"
+     FROM omegle_messages om1
+     WHERE from_ident IS NOT NULL and to_ident IS NOT NULL
+     GROUP BY to_ident||from_ident ) o1
+GROUP BY "initiator"
+ORDER BY 2
+LIMIT 10;
\ No newline at end of file