fefrei proposed a badge idea that grants users a badge when they have at least <POST_COUNT> posts in the category <CATEGORY_NAME> with at least <LIKE_COUNT> likes from users in the group <TEAM_NAME>. Lilly provided a SQL query to implement this badge, which was later modified by JammyDodger to fit the original requirement of granting the badge to users who have X number of posts with Y likes by staff, across all categories.
The final query provided by JammyDodger uses the badge_posts view, which only counts posts from public categories. To include all categories, the query can be modified to use the posts table instead, but this may require additional lines to exclude deleted posts or topics.
Lilly also experimented with using the actual post_action_code_id and group_id code, and developed a query with the help of a SQL assistant (GPT4bot). However, this query still had issues, and JammyDodger eventually provided a revised version that meets the original requirement.
The discussion highlights the complexity of writing badge queries and the importance of testing and refining them to ensure accuracy.
Grant a badge to everyone having posted at least <POST_COUNT> posts in the category <CATEGORY_NAME> that have received at least <LIKE_COUNT> likes by users in the group <TEAM_NAME>. Similar to the bug reporter badge here, but can require more than one like.
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE category_id = (
SELECT id FROM categories WHERE name ilike '<CATEGORY_NAME>'
) AND (
SELECT count(*)
FROM post_actions pa
WHERE pa.post_id = p.id
AND post_action_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike '<TEAM_NAME>' )
)
) >= <LIKE_COUNT>
AND p.post_number = 1
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST_COUNT>
<CATEGORY NAME> = 대소문자를 구분하는 카테고리 이름 (슬러그가 아님)
<GROUP> = 그룹 이름 (예: Staff, Trust_level_0)
<MINIMUM LIKE COUNT> = 설정하려는 최소 좋아요 수
<POST COUNT THRESHOLD> = 최소 게시글 수
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.category_id = (
SELECT id FROM categories WHERE name ilike '<CATEGORY NAME>'
) AND (
SELECT count(*)
FROM post_actions pa
WHERE pa.post_id = p.id
AND post_action_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike '<GROUP NAME>' )
)
) >= <MINIMUM LIKE COUNT>
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST COUNT THRESHOLD>
여러 카테고리를 처리하려면 이렇게 할 수 있습니다:
<CATEGORY NAMES> = 대소문자를 구분하는 카테고리 이름들
<GROUP> = 그룹 이름 (예: Staff, Trust_level_0)
<MINIMUM LIKE COUNT> = 설정하려는 최소 좋아요 수
<POST COUNT THRESHOLD> = 최소 게시글 수
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.category_id IN (
SELECT id FROM categories WHERE name ILIKE ANY (ARRAY['<CATEGORY NAME 1>', '<CATEGORY NAME 2>', '<CATEGORY NAME 3>'])
) AND (
SELECT count(*)
FROM post_actions pa
WHERE pa.post_id = p.id
AND post_action_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ILIKE '<GROUP>' )
)
) >= <MINIMUM LIKE COUNT>
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST COUNT THRESHOLD>
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE (
SELECT count(*)
FROM post_actions pa
WHERE pa.post_id = p.id
AND post_action_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ILIKE '<GROUP>' )
)
) >= <MINIMUM LIKE COUNT>
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST COUNT THRESHOLD>
그렇게 하면 작동할 것 같긴 한데, staff 그룹을 대상으로 실행하면 실패하는 것 같습니다. 그룹 이름으로 Staff와 staff를 모두 시도해 보고, 임시로 게시글 수와 좋아요 수를 1로 설정해 봤는데, 배지가 부여되지 않는다고 나옵니다. 제가 뭘 잘못하고 있는 걸까요?
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE (
SELECT count(*)
FROM post_actions pa
WHERE pa.post_id = p.id
AND post_action_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ILIKE 'staff' )
)
) >= 1
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= 1
아직 완전히 깨어난 상태는 아니에요. 배지 관련 쿼리를 제대로 처리하려면 차를 두 잔 정도 마셔야 할 것 같은데요, 최근 봇과 이런 종류의 쿼리에 대해 이야기하면서, 같은 결과를 얻기 위해 중첩 SELECT 쿼리를 사용하는 것보다 실제 post_action_code_id와 group_id 코드를 사용하는 것이 더 나은 것 같다고 생각하게 되었습니다.
posts, posts_actions, group_users 및 groups에 필요한 스키마 테이블을 가져오기 위해 다음을 수행했습니다.
SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<TABLE NAME>';
그 다음 그룹 ID를 모두 가져오기 위해 다음을 사용했습니다:
SELECT name, id FROM groups ORDER BY name
이후 필요한 모든 스키마 테이블을 포함시키고, Lola, 아니 GPTbot에게 실제 post_action_code ID와 group_id 코드를 사용하도록 지시했습니다. 그 후 몇 번의 논쟁과 수정을 거쳐 다음과 같은 결과를 얻었습니다. 여전히 Data Explorer에서는 작동하는 것 같지만, Badge Previewer에서는 아무것도 가져올 수 없습니다.
G = group_id
X = 최소 좋아요 수
Y = 최소 게시물 수
SELECT pa.user_id, MIN(pa.post_id) as post_id, COUNT(pa.post_id) as post_count, COUNT(pa.id) as like_count, MAX(pa.created_at) as granted_at
FROM post_actions pa
JOIN group_users gu ON gu.user_id = pa.user_id
WHERE gu.group_id = G AND pa.post_action_type_id = 2
GROUP BY pa.user_id
HAVING COUNT(pa.post_id) >= Y AND COUNT(pa.id) >= X
그런 유형의 쿼리에 또 다른 한계는 MIN(p.created_at) granted_at을 사용하면 첫 번째 날짜가 반환될 뿐, 예를 들어 10번째 날짜는 반환되지 않는다는 점입니다. MAX로 변경할 수는 있지만, 이미 10개 이상의 데이터가 있는 과거 데이터를 대상으로 실행하면 ‘맞지 않는’ 날짜가 반환될 수 있습니다.
아직 이 부분은 고민 중입니다.
ROW_NUMBER()을 사용해 어느 정도 성공을 거두었지만, 아직 구체적 결과는 없습니다.
그래도 이걸로 꽤 재미있고, SQL을 다시 배우고 더 좋은 쿼리를 작성하는 법을 익히는 데 도움이 되고 있어요. 로라 / GPT4봇을 SQL 어시스턴트로 쓰는 건 유용하지만, 그녀를 잘 이끌고 올바른 방식으로 질문해야 해요. 대부분의 스키마 테이블 정보에 접근할 수 있도록 하는 방법을 고민하고 있어요. 그래야 우리가 함께 작업하는 모든 쿼리 문제마다 매번 설명할 필요가 없으니까요. 테이블 스키마 정보를 제공하면 훨씬 더 좋은 결과를 얻어요. 코어에서 사용할 수 있는 스키마에 대한 링크를 주려고 했는데, 그랬더니 그냥 구글에서 이리저리 헤맬 뿐이었어요.
배지 쿼리 미리보기가 정상 작동하는 걸 알게 되면 그녀와 함께 작업해 보고 싶어요. SQL 연습과 배지 쿼리 작성에 익숙해져야 하거든요. 참고로 그녀는 그걸 고치지 못했고, 아직도 내 얼그레이 티를 충분히 뜨겁게 만들어 주지 않아요. 그래도 어젯밤 SQL 수업은 몇 년 만에 가장 좋은 데이트였어요.
일부 삭제된 좋아요(Likes)로 인해 테스트가 깨지는 혼란스러운 상황이 발생했는데, OP의 버전을 수정하여 여러분이 제시한 조건에 부합하도록 조정해 보았습니다.
SELECT p.user_id, MAX(p.created_at) granted_at
FROM badge_posts p
WHERE (SELECT COUNT(*)
FROM post_actions pa
WHERE pa.post_id = p.id
AND post_action_type_id = 2
AND deleted_at IS NULL
AND pa.user_id IN (SELECT gu.user_id FROM group_users gu WHERE gu.group_id = 3)
) >= 5
AND p.user_id >= 0
GROUP BY p.user_id
HAVING COUNT(*) >= 10
이 쿼리는 badge_posts 뷰를 기반으로 작동하므로 공개된 카테고리의 게시글만 집계됩니다. 이는 포럼/카테고리 구성에 따라 고려해야 할 사항일 수 있습니다. 또한 granted_at 값으로 CURRENT_TIMESTAMP를 사용하는 것도 다른 방법이지만, 이는 아마도 취향의 문제일 것입니다.
이걸 시도해 보세요. 제 인스턴스에서는 잘 작동했습니다.
여전히 내 환경에서는 작동하지 않아. 문제를 찾아가 보기 위해 다른 그룹 몇 개에 대해 실행해볼 거야.
