Q&A for users and developers of the Cardano cryptocurrency ecosystem
WITH DistinctTags AS ( SELECT Posts.Id AS [Post ID], Tags.TagName FROM Posts JOIN PostTags ON PostTags.PostId = Posts.Id JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN ('usability', 'accessibility') ) SELECT Posts.Id as [Post ID], -- Unique Post ID (SELECT STRING_AGG(TagName, ', ') FROM (SELECT DISTINCT TagName FROM DistinctTags WHERE DistinctTags.[Post ID] = Posts.Id) AS UniqueTags ) as [Searched Tags], -- Aggregated unique tags COUNT(DISTINCT Answers.Id) as [Answer Count], -- Count unique answers SUM(Answers.Score) as [Total Answer Score], -- Sum of answer scores CAST(AVG(CAST(Answers.Score AS float)) as numeric(6,1)) as [Average Answer Score], Posts.Tags as [Tagged With], Posts.Title as [Title], Posts.Body as [Body], Posts.CreationDate as [Creation Date], Users.DisplayName as [User Name], Users.Location as [User Location], Users.Views as [User Views], Users.Reputation as [User Reputation], Posts.ViewCount as [Post View Count], Posts.Score as [Post Score], Posts.AnswerCount as [Total Answer Count], Posts.AcceptedAnswerID as [Accepted Answer ID] FROM Posts -- Joining tables to get necessary details JOIN PostTags ON PostTags.PostId = Posts.Id JOIN Tags ON Tags.Id = PostTags.TagId JOIN Users ON Users.Id = Posts.OwnerUserId LEFT JOIN Posts AS Answers ON Answers.ParentId = Posts.Id -- Getting related answers WHERE Tags.TagName IN ('usability', 'accessibility') -- Filter posts with specified tags GROUP BY Posts.Id, Posts.Tags, Posts.Title, Posts.Body, Posts.CreationDate, Users.DisplayName, Users.Location, Users.Views, Users.Reputation, Posts.ViewCount, Posts.Score, Posts.AnswerCount, Posts.AcceptedAnswerID HAVING COUNT(DISTINCT Tags.TagName) = 2 -- Ensure both tags are present ORDER BY SUM(Answers.Score) DESC;