0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for users of Iota, the open-source cryptocurrency for IoT that does not use a blockchain

--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;


--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


        
        
      
--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
WITH tag_rows AS
(SELECT PostTags.*, TagName
FROM PostTags
INNER JOIN Tags
ON PostTags.TagId = Tags.Id
--WHERE PostId = 79
),

TagPairs AS (
    SELECT 
        t1.TagName AS tag1,
        t2.TagName AS tag2
    FROM 
        tag_rows t1
    INNER JOIN 
        tag_rows t2
    ON 
        t1.PostId = t2.PostId
    WHERE 
        t1.TagName < t2.TagName -- Ensure unique pairs (e.g., "ruby, rdoc" but not "rdoc, ruby")
)
SELECT TOP 10
    tag1,
    tag2,
    COUNT(*) AS combination_count
FROM 
    TagPairs
GROUP BY 
    tag1, tag2
ORDER BY 
    combination_count DESC


--ParsedTags AS (
--    SELECT 
--        Id,
--        value AS tag
--    FROM 
--        t1
--    CROSS APPLY 
--        STRING_SPLIT(Tags, '><')
--)

fork query

edited dec 3

created dec 3 anonymous