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
),

AWSVotes as (
  select * 
  from Votes v
  where v.PostId in (
    select id from AWSQuestionIds
    union
    select id from AWSAnswerIds
  )
),

AWSVotesCountries as (
  select 
    v.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 AWSVotes v
  join Users u on u.id = v.UserId
)

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

fork query

edited oct 31 19

created oct 31 19 Gabe.Hollombe