DECLARE @START_DATE_INCLUSIVE DATE = '2020-01-01' DECLARE...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Anime and Manga

Q&A for anime and manga fans

DECLARE @START_DATE_INCLUSIVE DATE = '2020-01-01'
DECLARE @END_DATE_EXCLUSIVE DATE = '2021-08-01' 

DECLARE @TagGroup TABLE(Name VARCHAR(50), TagId INT NOT NULL, RequireMobileTag BIT)

-- Cria agrupamento de Tags
INSERT INTO @TagGroup (name, TagId, RequireMobileTag) 
VALUES 
--  ('xamarin'      , (SELECT Id FROM Tags WHERE TagName = 'xamarin')              , 1), 
--  ('xamarin'      , (SELECT Id FROM Tags WHERE TagName = 'xamarin.ios')          , 0),
--  ('xamarin'      , (SELECT Id FROM Tags WHERE TagName = 'xamarin.android')      , 0),
--  ('xamarin'      , (SELECT Id FROM Tags WHERE TagName = 'xamarin.forms')        , 0),
--  ('ionic'        , (SELECT Id FROM Tags WHERE TagName = 'ionic4')               ),
--  ('ionic'        , (SELECT Id FROM Tags WHERE TagName = 'ionic3')               ),
--  ('ionic'        , (SELECT Id FROM Tags WHERE TagName = 'ionic2')               ),
--  ('ionic'        , (SELECT Id FROM Tags WHERE TagName = 'ionic-framework')      ),
--  ('ionic'        , (SELECT Id FROM Tags WHERE TagName = 'ionic')                ),
--  ('react-native' , (SELECT Id FROM Tags WHERE TagName = 'react-native-android') ),
--  ('react-native' , (SELECT Id FROM Tags WHERE TagName = 'react-native-ios')     ),
--  ('react-native' , (SELECT Id FROM Tags WHERE TagName = 'react-native')         ),
--  ('flutter'      , (SELECT Id FROM Tags WHERE TagName = 'flutter')              ),
--  ('nativescript' , (SELECT Id FROM Tags WHERE TagName = 'nativescript')         ),
--  ('firemonkey'   , (SELECT Id FROM Tags WHERE TagName = 'firemonkey')           ),
--  ('cordova'      , (SELECT Id FROM Tags WHERE TagName = 'cordova')              ),
  --('html-framework-7'      , (SELECT Id FROM Tags WHERE TagName = 'html-framework-7')              ),
  ('qt'      , (SELECT Id FROM Tags WHERE TagName = 'qt')              , 1),
  ('monaca'      , (SELECT Id FROM Tags WHERE TagName = 'monaca')              , 0)--,
--  ('phonegap'     , (SELECT Id FROM Tags WHERE TagName = 'phonegap')             , 0)


DECLARE @Frameworks TABLE(Name VARCHAR(50) NOT NULL PRIMARY KEY)
INSERT INTO @Frameworks (Name) SELECT DISTINCT Name FROM @TagGroup


DECLARE @PostWithMobileTags TABLE (PostId INT PRIMARY KEY)
INSERT INTO @PostWithMobileTags (PostId) 
	SELECT DISTINCT PostId FROM PostTags 
	JOIN Tags ON Tags.Id = PostTags.TagId
	WHERE Tags.TagName IN ('android', 'ios')


SELECT DISTINCT
	CreationDate,
	Framework,
	COUNT(PostId) NumberOfPosts
FROM (
	SELECT DISTINCT
		YEAR(PS.CreationDate) CreationDate,
		FR.name AS Framework,
		PS.Id AS PostId
	FROM Posts PS
	CROSS JOIN @Frameworks FR
	INNER JOIN PostTags PT ON PT.PostId = PS.Id
	WHERE 
		PS.CreationDate BETWEEN @START_DATE_INCLUSIVE AND @END_DATE_EXCLUSIVE 
		AND (
			( 
				PT.TagId IN (SELECT TagId FROM @TagGroup WHERE Name = FR.Name AND RequireMobileTag = 1)
				AND PS.Id IN (SELECT PostId FROM @PostWithMobileTags)
			) OR (
				PT.TagId IN (SELECT TagId FROM @TagGroup WHERE Name = FR.Name AND RequireMobileTag = 0)
			)
		)
	) T
GROUP BY CreationDate, Framework
ORDER BY 1

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results