Q&A about the site for users of Iota, the open-source cryptocurrency for IoT that does not use a blockchain
/* 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 * FROM #resultTable WHERE TagName = 'python' SELECT CreationDate, TagName, SUM(TagCount), ROW_NUMBER() OVER ( PARTITION BY CreationDate ORDER BY TagCount DESC) AS Rank -- SUM(TagCount) OVER (PARTITION BY CreationDate ORDER BY SUM(TagCount) DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeCount, --ROW_NUMBER() OVER (PARTITION BY CreationDate ORDER BY SUM(TagCount) DESC) AS Rankings FROM #resultTable GROUP BY CreationDate, TagName, TagCount HAVING 1= 1 ORDER BY CreationDate ASC--, CumulativeCount DESC; --- 5.uzdevums --- /* SELECT TOP 1 -- python Posts.Tags FROM Posts WHERE PostTypeId = 1 GROUP BY Posts.Tags ORDER BY COUNT(*) DESC */ --- 6.uzdevums ---