Comment by @punkess • Hey
awesome, instant collect :-)
are you using lens-public-data.polygon.public_profile_post for fetching the content?
Comments
- Thanks for collecting! Yup that's the table used here. Here's the query for the new one I just published with hashtags likes:
"""
WITH hashtags AS (
SELECT
hashtag,
post_id,
block_timestamp
FROM `lens-public-data.polygon.public_hashtag`
WHERE NOT REGEXP_CONTAINS(hashtag, r'^\\d+)
),
stats AS (
SELECT
publication_id,
total_amount_of_collects,
total_amount_of_mirrors,
total_amount_of_comments,
total_upvotes,
total_downvotes
FROM `lens-public-data.polygon.public_publication_stats`
),
hashtag_counts AS (
SELECT
post_id,
COUNT(hashtag) as hashtag_count
FROM hashtags
GROUP BY post_id
HAVING hashtag_count <= 3
),
aggregated_hashtags AS (
SELECT
hashtag,
COUNT(h.post_id) as post_count,
SUM(s.total_amount_of_collects) as total_collects,
SUM(s.total_amount_of_mirrors) as total_mirrors,
SUM(s.total_amount_of_comments) as total_comments,
SUM(s.total_upvotes) as total_upvotes,
SUM(s.total_downvotes) as total_downvotes
FROM hashtags h
INNER JOIN hashtag_counts hc ON hc.post_id = h.post_id
LEFT JOIN stats s ON s.publication_id = h.post_id
GROUP BY hashtag
)
SELECT *
FROM aggregated_hashtags
ORDER BY total_upvotes DESC
"""