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

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


SELECT 
  CreationDate, 
  TagName,
  -- 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 
  -- Rankings < 2
ORDER BY 
  CreationDate--, 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 ---

view current version

edited nov 14

created nov 14 Janis Jansons