Adobe Numbers

AdobeNumbers.sql

-- CLASSES QUERY : 4791 --
SELECT
	COUNT(*)                                                 
FROM
	ss_parent_classes pc
	LEFT JOIN
	(SELECT
		parenttag.parent_class_id,
		COUNT(*) as matching_tags
	FROM
		ss_parent_class_tags parenttag
		INNER JOIN
		ss_tags ON parenttag.tag_id = ss_tags.id
			AND ss_tags.name RLIKE
				'adobe|adobe dimension|after effects|creative cloud|dreamweaver|adobe illustrator|incopy|indesign|lightroom|media encoder|photoshop|premiere pro|premiere rush|spark|fresco'
	GROUP BY parenttag.parent_class_id
	) AS adobe_tags on pc.id = adobe_tags.parent_class_id
WHERE
	(pc.title RLIKE
		'adobe|adobe dimension|after effects|creative cloud|dreamweaver|adobe illustrator|incopy|indesign|lightroom|media encoder|photoshop|premiere pro|premiere rush|spark|fresco'
		OR pc.takeaway RLIKE
			'adobe|adobe dimension|after effects|creative cloud|dreamweaver|adobe illustrator|incopy|indesign|lightroom|media encoder|photoshop|premiere pro|premiere rush|spark|fresco'
		OR pc.description RLIKE
			'adobe|adobe dimension|after effects|creative cloud|dreamweaver|adobe illustrator|incopy|indesign|lightroom|media encoder|photoshop|premiere pro|premiere rush|spark|fresco'
		OR matching_tags > 0
	) 
	AND (
		pc.is_featured = 1 and
		pc.status_id = 1 and
		pc.is_hybrid = 1 and
		pc.is_forever = 1
	)

-- PROJECTS QUERY : 64418 --
SELECT
	COUNT(ss_projects.id)
FROM
	(SELECT
		pc.id                                                 
	FROM
	    ss_parent_classes pc
		LEFT JOIN
	    (SELECT
	    	parenttag.parent_class_id,
	       	COUNT(*) as matching_tags
	    FROM
	    	ss_parent_class_tags parenttag
	       	INNER JOIN
	       	ss_tags ON parenttag.tag_id = ss_tags.id
	       		AND ss_tags.name RLIKE
	        	'adobe|adobe dimension|after effects|creative cloud|dreamweaver|adobe illustrator|incopy|indesign|lightroom|media encoder|photoshop|premiere pro|premiere rush|spark|fresco'
	        GROUP BY parenttag.parent_class_id
	    ) AS adobe_tags on pc.id = adobe_tags.parent_class_id
	WHERE
		(pc.title RLIKE
	    	'adobe|adobe dimension|after effects|creative cloud|dreamweaver|adobe illustrator|incopy|indesign|lightroom|media encoder|photoshop|premiere pro|premiere rush|spark|fresco'
	    	OR
	        pc.takeaway RLIKE
	        'adobe|adobe dimension|after effects|creative cloud|dreamweaver|adobe illustrator|incopy|indesign|lightroom|media encoder|photoshop|premiere pro|premiere rush|spark|fresco'
	        OR
	        pc.description RLIKE
	        'adobe|adobe dimension|after effects|creative cloud|dreamweaver|adobe illustrator|incopy|indesign|lightroom|media encoder|photoshop|premiere pro|premiere rush|spark|fresco'
	        OR matching_tags > 0
	     ) AND (
	         pc.is_featured = 1 and
	         pc.status_id = 1 and
	         pc.is_hybrid = 1 and
	         pc.is_forever = 1
	       )
	) AS adobe_classes
LEFT JOIN
	ss_projects ON adobe_classes.id = ss_projects.parent_class_id
  
-- ENROLLMENTS QUERY : 4861362 --
SELECT
	COUNT(ss_rosters.id)
FROM
	(SELECT
		pc.id                                                 
	FROM
	    ss_parent_classes pc
		LEFT JOIN
	    (SELECT
	    	parenttag.parent_class_id,
	       	COUNT(*) as matching_tags
	    FROM
	    	ss_parent_class_tags parenttag
	       	INNER JOIN
	       	ss_tags ON parenttag.tag_id = ss_tags.id
	       		AND ss_tags.name RLIKE
	        	'adobe|adobe dimension|after effects|creative cloud|dreamweaver|adobe illustrator|incopy|indesign|lightroom|media encoder|photoshop|premiere pro|premiere rush|spark|fresco'
	        GROUP BY parenttag.parent_class_id
	    ) AS adobe_tags on pc.id = adobe_tags.parent_class_id
	WHERE
		(pc.title RLIKE
	    	'adobe|adobe dimension|after effects|creative cloud|dreamweaver|adobe illustrator|incopy|indesign|lightroom|media encoder|photoshop|premiere pro|premiere rush|spark|fresco'
	    	OR
	        pc.takeaway RLIKE
	        'adobe|adobe dimension|after effects|creative cloud|dreamweaver|adobe illustrator|incopy|indesign|lightroom|media encoder|photoshop|premiere pro|premiere rush|spark|fresco'
	        OR
	        pc.description RLIKE
	        'adobe|adobe dimension|after effects|creative cloud|dreamweaver|adobe illustrator|incopy|indesign|lightroom|media encoder|photoshop|premiere pro|premiere rush|spark|fresco'
	        OR matching_tags > 0
	     ) AND (
	         pc.is_featured = 1 and
	         pc.status_id = 1 and
	         pc.is_hybrid = 1 and
	         pc.is_forever = 1
	       )
	) AS adobe_classes
LEFT JOIN
	ss_rosters ON adobe_classes.id = ss_rosters.parent_class_id