-- 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 
         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
LIMIT 10;

-- 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(*) > 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;