Nochmal!
Ich habe gerade erst festgestellt, dass die Likes für TL3 die letzten 100 Tage sind! 
Korrigiert dafür:
WITH
t as (
select
CURRENT_TIMESTAMP - ((0 + 100) * (INTERVAL '1 days')) as start,
CURRENT_TIMESTAMP - (0 * (INTERVAL '1 days')) as end
),
-- Themaanzahl letzte 100 Tage 25%
-- das kleinere von 25% der in den letzten 100 Tagen erstellten Themen
-- ODER 500, die systemseitige Standardanforderung für TL3
tclhd AS (
SELECT LEAST(floor(count(id)*.25)::REAL,500) as all_topics
FROM topics, t
WHERE created_at > t.start
AND archetype = 'regular'
AND deleted_at is null
),
-- Beitraganzahl letzte 100 Tage 25%
-- das kleinere von 25% der in den letzten 100 Tagen erstellten Beiträge
-- ODER 20k, die systemseitige Standardanforderung für TL3
pclhd AS (
SELECT LEAST(FLOOR(count(id)*.25)::REAL,20000) AS all_posts
FROM t, posts
WHERE posts.created_at > start
AND posts.deleted_at is null
AND posts.hidden_at is null
AND posts.last_editor_id >0 -- Discobot & System ausschließen
AND (action_code is null OR action_code != 'assigned')
),
-- Vertrauensstufe 2 Benutzer
tl AS (
SELECT id as user_id
FROM users
WHERE trust_level = 2
),
-- Benutzer + Besuche & gelesene Beiträge letzte 100 Tage
pr AS (
SELECT user_id,
count(1) as visits,
sum(posts_read) as posts_read
FROM t, user_visits
INNER JOIN tl using (user_id)
WHERE visited_at > t.start
AND visited_at < t.end
GROUP BY user_id
ORDER BY visits DESC
),
-- Gelesene Beiträge aller Zeiten
prat as (
select user_id,
sum(posts_read) as posts_read
from t, user_visits
INNER JOIN tl using (user_id)
group by user_id
),
-- Antworten auf Themen
trt as (
select posts.user_id,
count(distinct topic_id) as replied_count
from t, posts
INNER JOIN tl using (user_id)
INNER JOIN topics ON topics.id = posts.topic_id
WHERE posts.created_at > t.start
AND posts.created_at < t.end
AND topics.user_id <> posts.user_id
AND posts.deleted_at IS NULL AND topics.deleted_at IS NULL
AND archetype = 'regular'
group by posts.user_id
),
-- Themen angesehen aller Zeiten
tvat as (
select tv.user_id,
COUNT(distinct tv.topic_id) AS topic_id
FROM topic_views tv
LEFT JOIN topics t on tv.topic_id=t.id
INNER JOIN tl on tv.user_id=tl.user_id
WHERE t.archetype = 'regular'
AND t.deleted_at is null
group by tv.user_id
),
-- Themen angesehen
tva AS (
SELECT tv.user_id,
COUNT(distinct tv.topic_id) AS topic_id
FROM t, topic_views tv
LEFT JOIN topics on topic_id=topics.id
INNER JOIN tl on tv.user_id=tl.user_id
WHERE
topics.archetype = 'regular'
AND topics.deleted_at is null
AND viewed_at > t.start
AND viewed_at < t.end
GROUP BY tv.user_id
),
likes_received_lhd AS (
SELECT ua.user_id
, count(*) as likes_received_lhd
FROM t, user_actions ua
JOIN posts p on p.id=ua.target_post_id
JOIN tl on ua.user_id=tl.user_id
WHERE ua.action_type=1
AND ua.created_at > t.start
AND ua.created_at < t.end
GROUP BY ua.user_id
),
likes_given_lhd AS (
SELECT user_id, count(*) as likes_given_lhd
FROM t, given_daily_likes
INNER JOIN tl using (user_id)
WHERE given_date > t.start
AND given_date < t.end
GROUP BY user_id
)
SELECT pr.user_id,
greatest(50-coalesce(pr.visits,0),0) as "Tage besucht LHD Lücke",
greatest(10-coalesce(trt.replied_count,0), 0) as "Themenantwort Lücke",
greatest(tclhd.all_topics-coalesce(tva.topic_id,0),0) AS "Themen angesehen LHD Lücke von 150",
greatest(200-coalesce(tvat.topic_id,0),0) as "Themen angesehen (AT) Lücke",
greatest(pclhd.all_posts - coalesce(pr.posts_read,0),0) as "Gelesene Beiträge LHD Lücke von 250",
greatest(500-coalesce(prat.posts_read,0),0) as "Gelesene Beiträge (AT) Lücke",
GREATEST(30-COALESCE(likes_given_lhd,0),0) as "Likes gegeben LHD Lücke",
GREATEST(20-COALESCE(likes_received_lhd,0),0) as "Likes erhalten LHD Lücke"
FROM pclhd, tclhd, pr
LEFT JOIN trt using (user_id)
LEFT JOIN tva USING (user_id)
LEFT JOIN tvat using (user_id)
LEFT JOIN prat using (user_id)
LEFT JOIN likes_received_lhd using (user_id)
LEFT JOIN likes_given_lhd using (user_id)
ORDER BY pr.visits DESC
LIMIT 25