0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

with QuestionIds as (
  select 
    distinct p.Id as Id,
    p.OwnerUserId as UserId,
    case 
      when t.tagName like 'amazon-%' then 'aws'
      when t.tagName like 'aws-%' then 'aws'      
      when t.tagName like 'google-cloud-%' then 'gcp'            
      when t.tagName like 'azure%' then 'azure'                  
    end as platform
  from Tags t
  JOIN PostTags pt ON t.Id = pt.TagId
  JOIN Posts p ON p.Id = pt.PostId --and p.PostTypeId = 1 -- PostTypeId 1: Question
  where 
    t.TagName like 'amazon-%'
    or t.TagName like 'aws-%'
    or t.TagName like 'google-cloud-%'
    or t.TagName like 'azure%'
),

AnswerIds as (
  select 
    p.Id as id, 
    p.OwnerUserId as UserId,
    q.platform as platform
  from Posts p
  join QuestionIds q on q.id = p.ParentId
  where p.posttypeid = 2
),

PostIds as (
  select * from QuestionIds
  union
  select * from AnswerIds
),

PostsCountries as (
  select 
    p.id,
    p.platform,
    case 
      when lower(u.location) like '%vietnam%' then 'Vietnam'
      when lower(u.location) like '%viet nam%' then 'Vietnam'
      when lower(u.location) like '%singapore%' then 'Singapore'
      when lower(u.location) like '%thailand%' then 'Thailand'
      when lower(u.location) like '%malaysia%' then 'Malaysia'
      when lower(u.location) like '%indonesia%' then 'Indonesia'
      when lower(u.location) like '%philippines%' then 'Philippines'
      else 'Other'
    end as Country
  from PostIds p
  join Users u on u.id = p.UserId
)

select 
  country,
  SUM(CASE WHEN platform = 'aws' THEN 1 END) as 'AWS',
  SUM(CASE WHEN platform = 'azure' THEN 1 END) as 'Azure',
  SUM(CASE WHEN platform = 'gcp' THEN 1 END) as 'GCP'
from 
  PostsCountries
group by country
order by 2 desc

fork query

edited oct 31 19

created oct 31 19 Gabe.Hollombe