Q&A for ebook publishers and readers
/* 1)Visus postus, kuriem tagos ir ir gan 'html', gan 'css' 2)Atlasīt vienotā sarakstā visu postu ID, kur ir vai nu top 10 pēc upvote vai top 10 pēc downvote 3)Atlasīt posta ID un tā pēdējo komentāru 4)Atlasīt kumulatīvo tendenci laikā pa visām dienām (pēc izveidošanas datuma) šajā gadā top 5 tēmām (tags) pēc postu skaita šogad. Sagaidāmais rezultāts: -)Datums -)Kumulatīvais postu skaits no gada sākuma 5)Atlasīt, kura ir biežāka tagu kombinācija pie posta 6)Uzrakstīt savus vērojumus par problēmām vai dīvainībām datos, kuras pamanītas veicot šos uzdevumus. Posts Id nav sekvenciali un ir dzēsti ID Posts Tabula tiek izmantot kā references tabula citam faktu tabulām Dublejas Refences uz neeksistējušiem ID 7)Uzrakstīt, kādi papildus jautājumi no biznesa viedokļa radās, pildot šos uzdevum */ --- 1.uzdevums --- /* SELECT -- TOP 10 * FROM Posts WHERE Id IN ( SELECT ptg.PostId FROM PostTags ptg JOIN Tags tg ON tg.Id = ptg.TagId WHERE tg.TagName IN ('css', 'html') GROUP BY ptg.PostId HAVING COUNT(*) > 1 ) AND PostTypeId = 1 */ --- 2.uzdevums --- -- 17/20 trūkst Id Posts tabulā /* SELECT pt.Id FROM Posts pt INNER JOIN ( SELECT TOP 10 PostId, COUNT(*) AS Count FROM Votes WHERE VoteTypeId = 2 GROUP BY PostId ORDER BY COUNT(*) DESC ) v ON pt.Id = v.PostId UNION ALL SELECT pt.Id FROM Posts pt INNER JOIN ( SELECT TOP 10 PostId, COUNT(*) AS Count FROM Votes WHERE VoteTypeId = 3 GROUP BY PostId ORDER BY COUNT(*) DESC ) v ON pt.Id = v.PostId */ --- 3.uzdevums --- /* DECLARE @PostId int = ##PostId## SELECT TOP 1 * FROM Posts WHERE 1=1 AND PostTypeId = 2 AND ParentId = @PostId -- 42 ORDER BY CreationDate DESC */ --- 4.uzdevums --- CREATE TABLE #postIdRef (PostId int, CreationDate Date) CREATE TABLE #resultTable (CreationDate Date, TagName nvarchar(35), TagCount int) INSERT #postIdRef SELECT ID, CAST(CreationDate AS Date) FROM Posts WHERE 1=1 AND CreationDate >= '2024-01-01' -- 490096 AND PostTypeId = 1 INSERT #resultTable SELECT CreationDate, TagName, COUNT(*) FROM PostTags JOIN #postIdRef ref ON ref.PostId = PostTags.PostId JOIN Tags t ON t.Id = PostTags.TagId GROUP BY CreationDate, TagName SELECT CreationDate, TagName, CumulativeCount FROM ( SELECT r1.CreationDate, r1.TagName, r1.TagCount, SUM(r2.TagCount) AS CumulativeCount, ROW_NUMBER() OVER (PARTITION BY r1.CreationDate ORDER BY SUM(r2.TagCount) DESC) AS RANK FROM #resultTable r1 JOIN #resultTable r2 ON r1.CreationDate >= r2.CreationDate AND r1.TagName =r2.TagName GROUP BY r1.CreationDate,r1.TagName, r1.TagCount ) a WHERE RANK <= 5 ORDER BY CreationDate, RANK --- 5.uzdevums --- /* SELECT TOP 1 -- python Posts.Tags FROM Posts WHERE PostTypeId = 1 GROUP BY Posts.Tags ORDER BY COUNT(*) DESC */ --- 6.uzdevums ---