0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for users and developers of the Cardano cryptocurrency ecosystem

WITH RankedPosts AS (
    SELECT 
        Id,
        Body,
        CreationDate,
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY CreationDate DESC) AS rn
    FROM 
        Posts
),

Recent_posts AS
(SELECT 
    Id,
    Body
FROM 
    RankedPosts
WHERE 
    rn = 1
),


--SELECT TOP 100 *
--FROM Users
--WHERE LOWER(Location) LIKE '%latvia%'

--SELECT Location, COUNT(Id) loc_count
--FROM Users
--WHERE LOWER(Location) LIKE '%latvia%' OR Location LIKE '%Riga %' OR Location LIKE '%Rīga %' OR Location LIKE '%Riga,%'
--GROUP BY Location
--ORDER BY loc_count DESC

--WITH valid_users AS
--(SELECT 

--SELECT Users.Id, COUNT(Badges.Id) AS badge_count
--FROM Users
--INNER JOIN Badges
--ON 
--Users.Id = Badges.UserId
--WHERE LOWER(Location) LIKE '%latvia%' OR Location LIKE '%Riga %' OR Location LIKE '%Rīga %' OR Location LIKE '%Riga,%'
--GROUP BY Users.Id
--HAVING COUNT(Badges.Id) > 100
--ORDER BY COUNT(Badges.Id) DESC;

lv_users_editors AS
(SELECT Users.Id AS user_id, Badges.Id AS badges_id
FROM Users
INNER JOIN Badges
ON Users.Id = Badges.UserId
WHERE (LOWER(Location) LIKE '%latvia%' OR Location LIKE '%Riga %' OR Location LIKE '%Rīga %' OR Location LIKE '%Riga,%') AND Name = 'Editor'
),

unique_users_editors AS
(SELECT DISTINCT user_id--, COUNT(badges_id) AS badge_count
FROM lv_users_editors
--GROUP BY user_id
--HAVING COUNT(badges_id) > 100
--ORDER BY badge_count DESC
)

SELECT unique_users_editors.*, Body
FROM unique_users_editors
INNER JOIN Recent_posts
ON unique_users_editors.user_id = Recent_posts.Id
--ORDER BY badge_count DESC


--WITH t1 AS
--(SELECT TOP 100 *
--FROM Posts
--INNER JOIN PostNotices
--ON Posts.Id = PostNotices.PostId
--)

--SELECT TOP 100 * 
--FROM PostNotices
--WHERE CreationDate BETWEEN '2021-01-01' AND '2021-12-31'

--SELECT *
--FROM Posts
--WHERE OwnerUserId = '1857266'
--ORDER BY CreationDate

--SELECT DISTINCT Name from Badges
--ORDER BY Name

fork query

edited dec 3

created dec 3 anonymous