0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Drupal developers and administrators

WITH month_questions_cnt_by_provider
AS
(
SELECT 
CONVERT(VARCHAR(6),CreationDate,112) AS yearmonth,
CASE WHEN p.tags like '%<google-maps>%'
     THEN 'google-map'
     WHEN p.tags like '%<mapbox>%'
     THEN 'mapbox'
     WHEN p.tags like '%<openstreetmap>%'
     THEN 'OSM'
     ELSE 'Other'
     END AS mapprovider,
COUNT(*) as count_questions
FROM Posts p
WHERE 
(p.tags like '%<google-maps>%'
 OR p.tags like '%<mapbox>%'
 OR p.tags like '%<openstreetmap>%' )
AND p.posttypeid = 1
AND p.CreationDate >= '2020-01-01'
GROUP BY CONVERT(VARCHAR(6),CreationDate,112),
CASE WHEN p.tags like '%<google-maps>%'
     THEN 'google-map'
     WHEN p.tags like '%<mapbox>%'
     THEN 'mapbox'
     WHEN p.tags like '%<openstreetmap>%'
     THEN 'OSM'
     ELSE 'Other'
     END
),

cnt_questions 
AS
(
SELECT yearmonth, mapprovider,
CAST(count_questions AS Decimal(10,2)) AS count_questions,
LAG(count_questions) OVER (
  PARTITION BY mapprovider ORDER BY yearmonth) AS prev_month_count_questions
FROM month_questions_cnt_by_provider
),

prcnt_change
AS
(
SELECT *,
CAST((count_questions/prev_month_count_questions - 1) * 100 AS DECIMAL(10,2)) 
  AS prcnt_change_mom
FROM cnt_questions
)

SELECT *, 
CONCAT(CASE WHEN prcnt_change_mom < 0 
            THEN N'↓ ' 
            WHEN prcnt_change_mom > 0 
            THEN N'↑ ' 
            ELSE '' END,CAST(prcnt_change_mom AS nVARCHAR(10))) AS string_prcnt_change_mom
FROM prcnt_change
ORDER BY 1,2

fork query

edited jun 15 21

created jun 15 21 anonymous