Hello Celo Sage Community
Let’s take a moment to delve into the mechanics of how we now choose our weekly top picks for approval within the Celo Sage program. We are committed to transparency, and as part of that, we want you to understand the guidelines and data that is instrumental to this process.
Which Proposals are Selected?
The proposals that will be selected are those submitted by and voted on by members of the Celo Sage, provided they comply with the proposal guidelines and community guidelines of the Celo Academy. Users are encouraged to contribute to maintaining an inclusive and positive environment by flagging posts that violate these guidelines.
Selecting Proposals with SQL
Our aim is to give visibility to Sage members and their contributions, allowing them to be actively involved in the proposal process. The guidelines and selection query ensures that we filter out votes from non-members, which, at this point, cannot be considered as votes from active community members. We also filter out proposals not created by Sage members, thereby allowing only Sage members to participate in the proposal creation process.
The query is designed to:
- Filter for proposals created by Sage members.
- Only consider votes cast by Sage members.
- Exclude any proposals that have been flagged and those flags have been accepted.
- Include only those proposals that are in the “New Requests” category.
- Rank the proposals based on the number of votes received.
- Limit the output to the top 15 proposals.
View Entire SQL Query
WITH sage_votes AS (
SELECT dvv.topic_id
FROM discourse_voting_votes dvv
JOIN group_users gu ON dvv.user_id = gu.user_id
JOIN groups g ON gu.group_id = g.id
WHERE g.name = 'Sage'
),
sage_users AS (
SELECT gu.user_id
FROM group_users gu
JOIN groups g ON gu.group_id = g.id
WHERE g.name = 'Sage'
),
vote_counts AS (
SELECT topic_id, COUNT(topic_id) AS "Number of Votes"
FROM sage_votes
GROUP BY topic_id
),
flagged_posts AS (
SELECT p.topic_id
FROM post_actions AS pa
JOIN posts AS p ON pa.post_id = p.id
JOIN reviewables AS r ON r.target_id = p.id AND r.target_type = 'Post'
JOIN post_action_types AS pat ON pa.post_action_type_id = pat.id
WHERE pat.is_flag = true AND r.status = 1
),
ranked_topics AS (
SELECT
topics.title AS "Proposal Title",
CONCAT('https://celo.academy/t/', topics.slug, '/', topics.id) AS "Link",
topics.excerpt AS "Topic Description",
users.username AS "Topic Owner",
vote_counts."Number of Votes",
categories.name AS "Category",
ROW_NUMBER() OVER (PARTITION BY topics.user_id ORDER BY vote_counts."Number of Votes" DESC) as rn
FROM
topics
JOIN
vote_counts ON topics.id = vote_counts.topic_id
JOIN
users ON topics.user_id = users.id
JOIN
categories ON topics.category_id = categories.id
WHERE
topics.user_id IN (SELECT user_id FROM sage_users)
AND topics.id NOT IN (SELECT topic_id FROM flagged_posts)
AND categories.name = 'New Requests'
)
SELECT
"Proposal Title",
"Link",
"Topic Description",
"Topic Owner",
"Category",
"Number of Votes"
FROM
ranked_topics
WHERE
rn = 1
ORDER BY
"Number of Votes" DESC
LIMIT 15
This query is also available on GitHub here and can be viewed by any Celo Sage here using the query This Week’s Top 15 Picks and selecting Run.
SQL Query Breakdown
1. WITH clause and CTEs (Common Table Expressions)
The SQL WITH clause allows you to give a sub-query block a name (a process also called sub-query factoring), which can be referenced in several places within the main SQL query.
WITH sage_votes AS (
SELECT dvv.topic_id
FROM discourse_voting_votes dvv
JOIN group_users gu ON dvv.user_id = gu.user_id
JOIN groups g ON gu.group_id = g.id
WHERE g.name = 'Sage'
)
The first CTE is sage_votes. It selects topic_id from the joined tables discourse_voting_votes, group_users, and groups. The JOIN operation connects rows from these tables where the user_id from dvv matches the user_id from gu, and the group_id from gu matches the id from g. The WHERE clause then filters the results to only include rows where the group name is ‘Sage’.
2. Continuing with CTEs
sage_users AS (
SELECT gu.user_id
FROM group_users gu
JOIN groups g ON gu.group_id = g.id
WHERE g.name = 'Sage'
)
The second CTE is sage_users. Similar to the first CTE, it uses a SELECT statement on the joined tables group_users and groups to select the user_id of users who are members of the ‘Sage’ group.
3. More CTEs
vote_counts AS (
SELECT topic_id, COUNT(topic_id) AS "Number of Votes"
FROM sage_votes
GROUP BY topic_id
)
The third CTE is vote_counts. It uses the COUNT function, which counts the number of times each topic_id appears in the sage_votes CTE. This gives the number of votes each topic has received from the ‘Sage’ group. The GROUP BY clause groups the result by topic_id.
4. Even more CTEs
flagged_posts AS (
SELECT p.topic_id
FROM post_actions AS pa
JOIN posts AS p ON pa.post_id = p.id
JOIN reviewables AS r ON r.target_id = p.id AND r.target_type = 'Post'
JOIN post_action_types AS pat ON pa.post_action_type_id = pat.id
WHERE pat.is_flag = true AND r.status = 1
)
The flagged_posts CTE identifies the topics that have been flagged and the flag has been accepted (r.status = 1). It joins the post_actions, posts, reviewables, and post_action_types tables and selects the topic_id of the flagged posts.
5. Finally, the last CTE
ranked_topics AS (
SELECT
topics.title AS "Proposal Title",
CONCAT('https://celo.academy/t/', topics.slug, '/', topics.id) AS "Link",
topics.excerpt AS "Topic Description",
users.username AS "Topic Owner",
vote_counts."Number of Votes",
categories.name AS "Category",
ROW_NUMBER() OVER (PARTITION BY topics.user_id ORDER BY vote_counts."Number of Votes" DESC) as rn
FROM
topics
JOIN
vote_counts ON topics.id = vote
This block of SQL code creates a Common Table Expression (CTE) named ‘ranked_topics’. It takes information from several tables - ‘topics’, ‘vote_counts’, ‘users’, and ‘categories’, and combines them into a new table. The key feature here is the ROW_NUMBER() function, which assigns a unique rank to each row within each ‘user_id’, based on the number of votes they have received in descending order.
Thank You Celo Sage Community
As we navigate through this journey of growth and improvement at Celo Academy, we want to take a moment to express our deepest gratitude to the Celo Sage community. Your contributions, insights, and active involvement have been instrumental in shaping the direction and success of the academy. Our commitment to inclusivity, transparency, and fostering a nurturing learning environment remains steadfast, and we look forward to continually improving our processes with your support.
The pivotal role that each member of the Celo Sage group plays in the proposal selection process underscores the collective nature of our community. It’s heartening to see the valuable contributions made and the robust dialogue and voting around them. This communal effort is what propels us towards a future of inclusive education and community building.
Keep proposing, keep voting, and continue being the amazing, dedicated individuals you are. Your belief in the power of an inclusive community is what fuels Celo Academy, and we are excited for the growth and evolution that the future holds, thanks to your support.
