-
Notifications
You must be signed in to change notification settings - Fork 371
/
queries.js
187 lines (186 loc) · 4.92 KB
/
queries.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
export const queryRankedContent = `
WITH
latest_published_root_contents AS (
SELECT
contents.id,
contents.owner_id,
contents.parent_id,
contents.slug,
contents.title,
contents.status,
contents.source_url,
contents.created_at,
contents.updated_at,
contents.published_at,
contents.deleted_at,
get_current_balance('content:tabcoin', contents.id) as tabcoins
FROM contents
WHERE
parent_id IS NULL
AND status = 'published'
AND published_at > NOW() - INTERVAL '1 week'
),
ranked_published_root_contents AS (
SELECT
*,
COUNT(*) OVER()::INTEGER as total_rows
FROM latest_published_root_contents
WHERE tabcoins > 0
ORDER BY
tabcoins DESC,
published_at DESC
),
top_one AS (
SELECT
*,
0 as rank_group
FROM ranked_published_root_contents
WHERE
tabcoins > 12
ORDER BY
tabcoins DESC,
published_at DESC
LIMIT 1
),
top_three AS (
SELECT * FROM top_one
UNION ALL
SELECT
*,
1 as rank_group
FROM ranked_published_root_contents
WHERE
published_at > NOW() - INTERVAL '2 days'
AND tabcoins > 6
AND id NOT IN (SELECT id FROM top_one)
ORDER BY
rank_group,
tabcoins DESC,
published_at DESC
LIMIT 3
),
top_1_hour AS (
SELECT * FROM top_three
UNION ALL
SELECT
*,
2 as rank_group
FROM ranked_published_root_contents
WHERE
published_at > NOW() - INTERVAL '1 hour'
AND id NOT IN (SELECT id FROM top_three)
ORDER BY
rank_group,
tabcoins DESC,
published_at DESC
LIMIT 6
),
top_6_hours AS (
SELECT * FROM top_1_hour
UNION ALL
SELECT
*,
3 as rank_group
FROM ranked_published_root_contents
WHERE
published_at > NOW() - INTERVAL '6 hours'
AND tabcoins > 1
AND id NOT IN (SELECT id FROM top_1_hour)
ORDER BY
rank_group,
tabcoins DESC,
published_at DESC
LIMIT 15
),
top_1_day AS (
SELECT * FROM top_6_hours
UNION ALL
SELECT
*,
4 as rank_group
FROM ranked_published_root_contents
WHERE
published_at > NOW() - INTERVAL '1 day'
AND id NOT IN (SELECT id FROM top_6_hours)
ORDER BY
rank_group,
tabcoins DESC,
published_at DESC
LIMIT 30
),
top_3_days AS (
SELECT * FROM top_1_day
UNION ALL
SELECT
*,
5 as rank_group
FROM ranked_published_root_contents
WHERE
published_at > NOW() - INTERVAL '3 days'
AND id NOT IN (SELECT id FROM top_1_day)
ORDER BY
rank_group,
tabcoins DESC,
published_at DESC
LIMIT 60
),
ranked AS (
SELECT * FROM top_3_days
UNION ALL
SELECT
*,
6 as rank_group
FROM ranked_published_root_contents
WHERE id NOT IN (SELECT id FROM top_3_days)
ORDER BY
rank_group,
tabcoins DESC,
published_at DESC
LIMIT $1
OFFSET $2
)
SELECT
ranked.id,
ranked.owner_id,
ranked.parent_id,
ranked.slug,
ranked.title,
ranked.status,
ranked.source_url,
ranked.created_at,
ranked.updated_at,
ranked.published_at,
ranked.deleted_at,
ranked.tabcoins,
ranked.rank_group,
ranked.total_rows,
users.username as owner_username,
(WITH RECURSIVE children AS
(SELECT id,
parent_id
FROM contents as all_contents
WHERE
all_contents.id = ranked.id
AND all_contents.status = 'published'
UNION ALL
SELECT
all_contents.id,
all_contents.parent_id
FROM contents as all_contents
INNER JOIN children ON all_contents.parent_id = children.id
WHERE all_contents.status = 'published'
)
SELECT count(children.id)::integer
FROM children
WHERE children.id NOT IN (ranked.id)
) as children_deep_count
FROM ranked
INNER JOIN users ON ranked.owner_id = users.id
ORDER BY
rank_group,
tabcoins DESC,
published_at DESC;
`;
export default Object.freeze({
queryRankedContent,
});