SO_EPAM_Krk_positive_answers_rate

0

Please login or register to vote for this query.

(click on this box to dismiss)

Interpersonal Skills

Q&A for people looking to improve their interpersonal communication skills

declare @platform_agents table (user_id int);
insert into @platform_agents values (10161306), (12410332), (12767257), (12857703);

declare @bigdata_agents table (user_id int);
insert into @bigdata_agents values (7090016), (9928809), (12571387), (12518487);

declare @infra_agents table (user_id int);
insert into @infra_agents values (11602913), (12257250), (10892354), (12428794);


Select Week, Shard, sum(t1.Total) as Total, sum(t1.Positive) as Positive, sum(t1.Positive)*100/sum(Total) as Rate
FROM (
    select 
      CASE
        WHEN pa.owneruserid in (select user_id from @platform_agents) THEN 'Platform'
        WHEN pa.owneruserid in (select user_id from @infra_agents) THEN 'Infra'
        WHEN pa.owneruserid in (select user_id from @bigdata_agents) THEN 'Bigdata'
        ELSE 'K8s'
      END as Shard,
      DATEPART (wk, pa.CreationDate) as Week, count (pa.Id) as Total, 0 as Positive
    from 
      posts pa,
      posts pq
    where 
      pa.PostTypeId=2 
      and pq.PostTypeId=1
      and pq.Id=pa.ParentId
      and pa.owneruserid in (11101419,7090016,12153576,12257134,12201084,11977760,1466573,11207414,11714114,11602913,11102471,9928809,12237732,11300382,12014434,3156333,11148139,10347794,12186585,9929015,9521610,12257250,11560878,10892354,10161306,12410332,12428794,12524159,11901958,12571387,12518487,12767257,12857703)
      and (lower(pq.Tags) like '%kubernetes%'
      or lower(pq.Tags) like '%minikube%'
      or lower(pq.Tags) like '%istio%'
      or lower(pq.Tags) like '%gcp%'
      or lower(pq.Tags) like '%k8s%'
      or lower(pq.Tags) like '%gke%'
      or lower(pq.Tags) like '%google-kubernetes-engine%'
      or lower(pq.Tags) like '%google-cloud-firestore%'
      or lower(pq.Tags) like '%google-cloud-platform%'
      or lower(pq.Tags) like '%google-compute-engine%'
      or lower(pq.Tags) like '%google-cloud-composer%'
      or lower(pq.Tags) like '%google-data-studio%'
      or lower(pq.Tags) like '%google-bigquery%'
      or lower(pq.Tags) like '%google-app-engine%'
      or lower(pq.Tags) like '%firebase%'
      or lower(pq.Tags) like '%kubectl%'
      or lower(pq.Tags) like '%airflow%'
      )
      and pa.CreationDate >= ##startdate:string##
      and pa.CreationDate <= ##enddate:string##
    group by DATEPART (wk, pa.CreationDate), pa.owneruserid
    UNION ALL
    select 
      CASE
        WHEN pa.owneruserid in (select user_id from @platform_agents) THEN 'Platform'
        WHEN pa.owneruserid in (select user_id from @infra_agents) THEN 'Infra'
        WHEN pa.owneruserid in (select user_id from @bigdata_agents) THEN 'Bigdata'
        ELSE 'K8s'
      END as Shard,
    DATEPART (wk, pa.CreationDate) as Week,0 as Total,count (pa.Id) as Positive
    from 
      posts pa,
      posts pq
    where 
      pa.PostTypeId=2 
      and pq.PostTypeId=1
      and pq.Id=pa.ParentId
      and pa.owneruserid in (11101419,7090016,12153576,12257134,12201084,11977760,1466573,11207414,11714114,11602913,11102471,9928809,12237732,11300382,12014434,3156333,11148139,10347794,12186585,9929015,9521610,12257250,11560878,10892354,10161306,12410332,12428794,12524159,11901958,12571387,12518487,12767257,12857703)
      and (lower(pq.Tags) like '%kubernetes%'
      or lower(pq.Tags) like '%minikube%'
      or lower(pq.Tags) like '%istio%'
      or lower(pq.Tags) like '%gcp%'
      or lower(pq.Tags) like '%k8s%'
      or lower(pq.Tags) like '%gke%'
      or lower(pq.Tags) like '%google-kubernetes-engine%'
      or lower(pq.Tags) like '%google-cloud-firestore%'
      or lower(pq.Tags) like '%google-cloud-platform%'
      or lower(pq.Tags) like '%google-compute-engine%'
      or lower(pq.Tags) like '%google-cloud-composer%'
      or lower(pq.Tags) like '%google-data-studio%'
      or lower(pq.Tags) like '%google-bigquery%'
      or lower(pq.Tags) like '%google-app-engine%'
      or lower(pq.Tags) like '%firebase%'
      or lower(pq.Tags) like '%kubectl%'
      or lower(pq.Tags) like '%airflow%'
      )
      and pa.CreationDate >= ##startdate:string##
      and pa.CreationDate <= ##enddate:string##
      and (pa.Score > 0 or pa.AcceptedAnswerId <> 0)
    group by DATEPART (wk, pa.CreationDate),pa.owneruserid
) as t1
group by t1.Week, t1.Shard
UNION ALL
Select t2.Week,t2.Shard, sum(t2.Total) as Total,sum(t2.Positive) as Positive,sum(t2.Positive)*100/sum(t2.Total) as Rate
from
(
select 
      'All Shards' as Shard,
    DATEPART (wk, pa.CreationDate) as Week,
    count (pa.Id) as Total, 
    0 as Positive
    from 
      posts pa,
      posts pq
    where 
      pa.PostTypeId=2 
      and pq.PostTypeId=1
      and pq.Id=pa.ParentId
      and pa.owneruserid in (11101419,7090016,12153576,12257134,12201084,11977760,1466573,11207414,11714114,11602913,11102471,9928809,12237732,11300382,12014434,3156333,11148139,10347794,12186585,9929015,9521610,12257250,11560878,10892354,10161306,12410332,12428794,12524159,11901958,12571387,12518487,12767257,12857703)
            and (lower(pq.Tags) like '%kubernetes%'
      or lower(pq.Tags) like '%minikube%'
      or lower(pq.Tags) like '%istio%'
      or lower(pq.Tags) like '%gcp%'
      or lower(pq.Tags) like '%k8s%'
      or lower(pq.Tags) like '%gke%'
      or lower(pq.Tags) like '%google-kubernetes-engine%'
      or lower(pq.Tags) like '%google-cloud-firestore%'
      or lower(pq.Tags) like '%google-cloud-platform%'
      or lower(pq.Tags) like '%google-compute-engine%'
      or lower(pq.Tags) like '%google-cloud-composer%'
      or lower(pq.Tags) like '%google-data-studio%'
      or lower(pq.Tags) like '%google-bigquery%'
      or lower(pq.Tags) like '%google-app-engine%'
      or lower(pq.Tags) like '%firebase%'
      or lower(pq.Tags) like '%kubectl%'
      or lower(pq.Tags) like '%airflow%'
      )
      and pa.CreationDate >= ##startdate:string##
      and pa.CreationDate <= ##enddate:string##
    group by DATEPART (wk, pa.CreationDate)
UNION ALL
    select 
      'All Shards' as Shard,
    DATEPART (wk, pa.CreationDate) as Week,
    0 as Total,
    count (pa.Id) as Positive
    from 
      posts pa,
      posts pq
    where 
      pa.PostTypeId=2 
      and pq.PostTypeId=1
      and pq.Id=pa.ParentId
      and pa.owneruserid in (11101419,7090016,12153576,12257134,12201084,11977760,1466573,11207414,11714114,11602913,11102471,9928809,12237732,11300382,12014434,3156333,11148139,10347794,12186585,9929015,9521610,12257250,11560878,10892354,10161306,12410332,12428794,12524159,11901958,12571387,12518487,12767257,12857703)
      and (lower(pq.Tags) like '%kubernetes%'
      or lower(pq.Tags) like '%minikube%'
      or lower(pq.Tags) like '%istio%'
      or lower(pq.Tags) like '%gcp%'
      or lower(pq.Tags) like '%k8s%'
      or lower(pq.Tags) like '%gke%'
      or lower(pq.Tags) like '%google-kubernetes-engine%'
      or lower(pq.Tags) like '%google-cloud-firestore%'
      or lower(pq.Tags) like '%google-cloud-platform%'
      or lower(pq.Tags) like '%google-compute-engine%'
      or lower(pq.Tags) like '%google-cloud-composer%'
      or lower(pq.Tags) like '%google-data-studio%'
      or lower(pq.Tags) like '%google-bigquery%'
      or lower(pq.Tags) like '%google-app-engine%'
      or lower(pq.Tags) like '%firebase%'
      or lower(pq.Tags) like '%kubectl%'
      or lower(pq.Tags) like '%airflow%'
      )
      and pa.CreationDate >= ##startdate:string##
      and pa.CreationDate <= ##enddate:string##
      and (pa.Score > 0 or pa.AcceptedAnswerId <> 0)
      group by DATEPART (wk, pa.CreationDate)) as t2
group by t2.Shard, t2.Week
order by Week

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results