根据上文的征文规则，今天应该是投票阶段了。本期超过10篇，我们按照文末的SQL来进行排名。本期多选投票（最多3票），请为你最喜爱的帖子投票吧（投票列表按发表时间排序）：
本期你最喜爱的文章
【建站提速】一文讲透 Cloudflare 优选加速，一个域名也能简单实现
0
投票人
[!warning]
误投的佬友可以撤销后重新投票。
附上统计SQL给佬友们 review（日期均已转换为+8）：
SELECT * FROM (
WITH topic_stats AS (
SELECT
t.id AS topic_id,
t.title AS topic_title,
t.user_id AS author_id,
u.username AS author_username,
t.created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Shanghai' AS created_at_cst,
-- 统计点赞数
COALESCE((
SELECT COUNT(DISTINCT pa.user_id)
FROM post_actions pa
JOIN posts p ON p.id = pa.post_id
WHERE p.topic_id = t.id
AND p.post_number = 1 -- 主楼
AND pa.post_action_type_id = 2 -- 点赞事件
AND pa.deleted_at IS NULL
AND pa.user_id != t.user_id -- 排除帖主
AND pa.created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Shanghai' < :end_date_cst
), 0) AS likes_count,
-- 统计回帖数
COALESCE((
SELECT COUNT(*)
FROM posts p
WHERE p.topic_id = t.id
AND p.post_number > 1 -- 跟帖
AND p.post_type = 1 -- 普通回帖
AND p.user_id != t.user_id -- 排除帖主
AND p.deleted_at IS NULL
AND p.hidden = false
AND p.created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Shanghai' < :end_date_cst
), 0) AS reply_count
FROM topics t
JOIN users u ON u.id = t.user_id
WHERE t.category_id = :category_id
AND t.deleted_at IS NULL
AND t.visible = true
AND t.archetype = 'regular'
AND t.created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Shanghai' < :end_date_cst
)
SELECT
concat('https://idcflare.com/t/topic/', topic_id) as topic_link,
topic_id as tid,
topic_title,
author_username,
created_at_cst,
likes_count,
reply_count,
(likes_count * 1 + reply_count * 2) AS total_score,
ROW_NUMBER() OVER (ORDER BY (likes_count * 1 + reply_count * 2) DESC, topic_id DESC) AS ranking
FROM topic_stats
WHERE (likes_count * 1 + reply_count * 2) > 0
ORDER BY total_score DESC, topic_id DESC
LIMIT 10
) AS top10
ORDER BY created_at_cst ASC
支持！
