0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

with AWSQuestionIds as (
  select p.Id as id
  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 = 'amazon-web-services'
),

AWSAnswerIds as (
  select p.Id as id
  from Posts p
  join AWSQuestionIds q on q.id = p.ParentId
  where p.posttypeid = 2
),

AWSPosts as (
  select * 
  from Posts p
  where p.Id in (
    select id from AWSQuestionIds
    union
    select id from AWSAnswerIds
  )
),

AWSPostCountries as (
  select 
    p.id,
    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 AWSPosts p
  join Users u on u.id = p.OwnerUserId
)

select 
  country,
  count(*) as cnt
from 
  AWSPostCountries 
group by 
  country
order by 
  cnt desc

fork query

created oct 31 19 Gabe.Hollombe