ks--ks

data(mushroom)
X = mushroom[, -1]
y = as.numeric(mushroom[, 1])            # конвертировать метки в числа

ks--ks

WITH actions AS
    (
        SELECT  ca.created_at::date as actdate,
                COUNT(*) AS act,
                CASE when ca.state IN ('CA', 'WA', 'DC', 'NY', 'IL') THEN ca.state ELSE 'other' END AS state,
                CASE WHEN ca.current_medium IN ('source1', 'source2', 'source3') THEN ca.current_medium ELSE 'other' END AS source,
                lower('id-' || ca.created_at::date ||
                      '-' || CASE when ca.state IN ('CA', 'WA', 'DC', 'NY', 'IL') THEN ca.state ELSE 'other' END ||
                      '-' || CASE WHEN ca.current_medium IN ('source1', 'source2', 'source3') THEN ca.current_medium ELSE 'other' END
                ) AS id

ks--ks

action_view as
    (
        SELECT created_at::date AS viewdate,
            COUNT(av.userid) AS act_v,
            CASE when av.state IN ('CA', 'WA', 'DC', 'NY', 'IL') THEN av.state ELSE 'other' END AS state,
            CASE WHEN av.current_medium IN ('source1', 'source2', 'source3') THEN av.current_medium ELSE 'other' END AS source,
            lower('id-' || av.created_at::date ||
                  '-' || CASE when av.state IN ('CA', 'WA', 'DC', 'NY', 'IL') THEN av.state ELSE 'other' END ||
                  '-' || CASE WHEN av.current_medium IN ('source1', 'source2', 'source3') THEN av.current_medium ELSE 'other' END
            ) AS idp

ks--ks

    SELECT av.sapdate             AS date,
           lower(av.state)        AS state,
           lower(av.source)       AS source,
           av.act_v               AS act_view,
           a.act::int             AS act,
    FROM action_view av
    LEFT OUTER JOIN actions a ON av.idp = a.id
    ORDER BY date;

ks--ks

WITH

recruits AS (
SELECT recruiter_id AS user_id
	, item_name
	, COUNT(DISTINCT recruited_id) AS recruits
FROM recruit_table
WHERE created_at::DATE BETWEEN '2017-01-01'-180 AND '2017-01-08'
GROUP BY 1, 2
)

ks--ks

, influence AS (
SELECT user_id 
	, recruit_score 
	, share_score 
	, days_share_score 
	, channel_share_score 
	, NTILE(100) OVER (ORDER BY recruit_score DESC, share_score DESC, days_share_score DESC, channel_share_score DESC)

FROM (
	SELECT a.user_id 

ks--ks

, NTILE(100) OVER (ORDER BY recruit_score DESC, share_score DESC, days_share_score DESC, channel_share_score DESC)

ks--ks

import numpy as np #linear algebra
import pandas as pd #data processing 
import seaborn as sns #statistical graph package 
import matplotlib.pyplot as plt #plot package for visualisations 
import pandasql as ps #sql package 

ks--ks

#plt.style.use('bmh') #setting up 'bmh' as "Bayesian Methods for Hackers" style sheet 
plt.style.use('ggplot') #R ggplot style 

ks--ks

print(plt.style.available)