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

/* B2B. Getting daily or monthly retention for free users 

Input tables: 
signups - user_id, s.signup_date
activity - user_id, activity_date
*/  


-- CREATE VIEW cohort_user_retention AS

ks--ks

SELECT active.num_days_active
, COUNT(DISTINCT active.user_id) AS num_users -- get how many users have a number of active days
FROM (
SELECT a.user_id, COUNT(DISTINCT a.created_at) AS num_days_active
FROM activity a
GROUP BY a.user_id) AS active
LEFT JOIN activity a2 ON a2.user_id = active.user_id
WHERE a2.created_at >= CURRENT_DATE - 28
GROUP BY active.num_days_active
ORDER BY active.num_days_active ASC;

ks--ks

WITH aggregated_subs AS (
SELECT
    USER_ID
    ,PRODUCT_ID
    ,TRANSACTION_START
    ,TRANSACTION_DATE_DUE
    ,ROW_NUMBER() OVER (PARTITION BY PRODUCT_ID ORDER BY TRANSACTION_START) AS rn
    ,LAG(TRANSACTION_DATE_DUE, 1) OVER (PARTITION BY PRODUCT_ID ORDER BY TRANSACTION_DATE_DUE) AS previous_sub_end
FROM SUBSCRIPTIONS
-- ORDER BY TRANSACTION_START, rn

ks--ks

-- sample data and assign new or returning user type
sampledata as (
select a.user_id, 
    a.created_at
, case 
    when a.created_at between a.firstdate and a.firstdate+27 then 'new' 
        else 'returning' 
        end as type
from (
    select user_id 

ks--ks

#We have a loud talking parrot. The "hour" parameter is the current hour time in the range 0..23. 
#We are in trouble if the parrot is talking and the hour is before 7 or after 20. 
#Return True if we are in trouble.

def parrot_trouble(talking, hour):
  #hour < 7 or hour > 20
  return (talking and (hour - 7 or hour > 20))

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

'''We have two monkeys, a and b, and the parameters a_smile and b_smile 
indicate if each is smiling. We are in trouble if they are both smiling 
or if neither of them is smiling. Return True if we are in trouble.'''

'''
monkey_trouble(True, True) → True
monkey_trouble(False, False) → True
monkey_trouble(True, False) → False
'''

ks--ks

'''Given an int n, return the absolute difference between n and 21, 
except return double the absolute difference if n is over 21.'''

def diff21(n):
  if n <= 21:
    return 21-n
  else:
    return (n - 21) * 2

ks--ks

'''You have the following list of numbers: 

n = [1,2,3,4,5,6]

Iterate over this list, printing out each list value multiplied by 10.'''

#Solution 1:
for x in n:
    print (x * 10)