All Queries For Redshift

select date(ts), count(distinct(visitorid)) from apilogs where site='android' and appversion = '1.11.4' and date(ts) >='2017-01-17' group by 1 order by date(ts);


select * from apilogs where site='android' and appversion = '1.8' and date(ts) > '2016-12-31' and action = 'AccountController-trackorderDetail' order by date(ts) limit 10;

select * from apilogs where action='AccountController-trackorderDetail' limit 10;


select date(ts), count(distinct(visitorid)) from apilogs where site='android' and action='SearchController-search' and appversion = '1.8.1' and date(ts) >= '2017-01-01' group by 1 order by date(ts);

Select date(ts), count(distinct(visitorid)) from apilogs where action ='BoutiqueController-recentlyViewedBoutiques' and site='android' and date(ts) >= '2016-12-01' group by 1 order by date(ts);


Select date(ts), appversion, count(distinct(visitorid)) from apilogs where site='android' and date(ts) >= '2016-12-20'
and appversion in ('1.0', '1.1', '1.1.1', '1.1.2', '1.2', '1.2.1', '1.3', '1.3.1', '1.3.2', '1.4', '1.5', '1.5.1', '1.6', '1.7', '1.7.1', '1.8', '1.8.1', '') group by  1,2 order by date(ts);

select date(ts), site, count(visitorid) from apilogs where action='AccountController-trackorderDetail' and date(ts) >= '2016-12-01' and site in ('android', 'ios', 'web', 'mobile') group by 1,2 order by date(ts);


select date(ts), count(distinct(visitorid)) from apilogs where site='android' and action='BoutiqueController-getBoutique' and appversion <= '1.8' and date(ts) >= '2016-12-31' group by 1 order by date(ts);

select date(ts), count(distinct(visitorid)) from apilogs where site='android' and date(ts) > '2016-12-20' and
appversion = '1.10.2' group by 1 order by date(ts) limit 100;

select date(ts), count(distinct(visitorid)) from apilogs where site='android' and action='BoutiqueController-getCategoryPage' and appVersion='1.8.1' group by 1 order by date(ts);

select date(ts), count(distinct(visitorid)) from apilogs where site='android' and appversion='1.11.3' and date(ts) > '2016-11-27' group by 1 order by date(ts);

select * from apilogs where action='AccountController-trackorderDetail' and site='android' limit 100;

select distinct(customertype) from apilogs;

select date(ts), count(distinct(visitorid)) from apilogs where site='android' and appversion='1.10.1'and date(ts) > '2016-10-08' group by 1 order by 1;

select distinct(site) from apilogs where date(ts) = '2016-12-23';


/* Order Return Data*/
select date(ts), count(distinct(visitorid)) from apilogs where action='AccountController-orderReturn' and site = 'android' group by 1 order by date(ts);

select date(ts), count(distinct(visitorid)) from apilogs where action='AccountController-orderReturn' and date(ts) >= '2016-12-01' group by 1 order by date(ts);

Select date(ts), site, count(distinct(uri)) from apilogs where action='AccountController-trackorderDetail' and date(ts) >= '2016-12-01' and site in ('android', 'mobile', 'web', 'ios') group by 1,2 order by date(ts);

Select date(ts), appversion, count(distinct(visitorid)) from apilogs where site='android' and date(ts) >= '2016-12-20'
and appversion in ('1.0', '1.1', '1.1.1', '1.1.2', '1.2', '1.2.1', '1.3', '1.3.1', '1.3.2', '1.4', '1.5', '1.5.1', '1.6', '1.7', '1.7.1', '1.8', '1.8.1', '') group by  1,2 order by date(ts);

/* Order Tracking Data*/
select date(ts), count(distinct(visitorid)) from apilogs where action='AccountController-trackorderDetail' and site ='android' group by 1 order by date(ts);



select * from apilogs where action='ProductController-getProduct' and site='android' and date(ts) > '2016-10-28' and appversion='1.10.2' order by ts limit 10;

Select ts, elapsed from apilogs where action='ShoppingCartController-showShoppingCart' and site='android' and date(ts) > '2016-10-13' and elapsed in (Select max(elapsed) from apilogs where action='ShoppingCartController-showShoppingCart' and site='android' and date(ts) > '2016-10-13');

Select os, appversion from apilogs where date(ts) = '2016-11-30' and action='ProductController-getProduct' and site='android' and appversion='1.11' and os='android_6.0.1';

select date(ts) as Date, max(elapsed) as MaxElapsed, avg(elapsed) as AvgElapsed from apilogs where action='BoutiqueController-getSalePlan' and site='android' and date(ts) > '2016-10-28' group by 1 order by 1;

select date(ts) as Date, count(distinct visitorid) as Visitorid from apilogs where action='BoutiqueController-getSalePlan' and site='android' and date(ts) > '2016-10-28' and elapsed > 1000 group by 1 order by 1;


select date(ts) as Date, max(elapsed) as MaxElapsed, avg(elapsed) as AvgElapsed from apilogs where action='ProductController-getProduct' and site='android' and date(ts) > '2016-10-28' group by 1 order by 1;

select date(ts) as Date, count(distinct visitorid) as Visitorid from apilogs where action='ProductController-getProduct' and site='android' and date(ts) > '2016-10-28' and elapsed > 1000 group by 1 order by 1;

/* Max min of elapsed time of Shopping Cart Response Time */

/* Max min of elapsed time of PDP Response Time */
select date(ts), avg(elapsed) from apilogs where action='ProductController-getProduct' and site='android' and date(ts) > '2016-10-28' group by 1 order by 1;


select date(ts), count(distinct(uri)) from apilogs where site='android' and action='AccountController-trackorderDetail' and date(ts) > '2016-11-29';



Select date(ts), case when appversion in ('1.11') then 'return' else 'old' end as app_version_category, count(distinct(visitorid)) from apilogs where site='ios' and date(ts) >= '2016-12-01'
-- and appversion in ('1.0', '1.1', '1.1.1', '1.1.2', '1.2', '1.2.1', '1.3', '1.3.1', '1.3.2', '1.4', '1.5', '1.5.1', '1.6', '1.7', '1.7.1', '1.8', '1.8.1', '1.8.2', '1.9', '1.10', '1.10.1', '1.10.2', '1.11', '1.11.1', '1.11.2', '1.11.3')
group by  1,2 order by date(ts);