WITH 
  impressions AS (
    SELECT uuid, TIMESTAMP
    FROM feed_events
    WHERE event = 'show'
  ),
  clicks AS (
    SELECT uuid, TIMESTAMP
    FROM feed_events
    WHERE event = 'click'
  ),
  sessions AS (
    SELECT uuid, MIN(TIMESTAMP) AS session_start
    FROM feed_events
    WHERE event = 'open'
    GROUP BY uuid
  ),
  relevant_impressions AS (
    SELECT i.uuid, i.timestamp
    FROM impressions i
    JOIN sessions s ON i.uuid = s.uuid AND i.timestamp > s.session_start
  ),
  ctrs AS (
    SELECT r.uuid, CAST(COUNT(DISTINCT c.timestamp) AS REAL) / COUNT(DISTINCT r.timestamp) AS ctr
    FROM relevant_impressions r
    LEFT JOIN clicks c ON r.uuid = c.uuid AND r.timestamp = c.timestamp
    GROUP BY r.uuid
  )
SELECT uuid, ROUND(ctr, 2) AS ctr
FROM ctrs
ORDER BY ctr DESC, uuid
LIMIT 10;