Python Data Science Coding Reference

UTM parameters  are a way of tracking visits to a website. Developers, marketers, and analysts use them to capture information like the time, attribution source, and attribution medium for each user visit. First-touch attribution  only considers the first source for each customer. This is a good way of knowing how visitors initially discover a website. Last-touch attribution  only considers the last source for each customer. This is a good way of knowing how visitors are drawn back to a website, especially for making a final purchase. Find first and last touches by grouping  page_visits  by  user_id  and finding the  MIN  and  MAX  of  timestamp . To find first- and last-touch attribution, join that table back with the original  page_visits  table on  user_id  and  timestamp . WITH  last_touch  AS   (    SELECT  user_id,      MAX ( timestamp )   as ...

WITH months AS (SELECT '2017-01-01' as first_day, '2017-01-31' as last_day UNION SELECT '2017-02-01' as first_day, '2017-02-28' as last_day UNION SELECT '2017-03-01' as first_day, '2017-03-31' as last_day ), cross_join AS (SELECT * FROM subscriptions CROSS JOIN months ), status as (SELECT id, first_day as 'month', CASE WHEN (subscription_start < first_day) AND (subscription_end > first_day) OR (subscription_end IS NULL) AND (segment = 87) THEN 1 ELSE 0 END AS is_active_87, CASE WHEN (subscription_start < first_day) AND (subscription_end > first_day) OR (subscription_end IS NULL) AND (segment = 30) THEN 1 ELSE 0 END AS is_active_30, CASE WHEN (subscription_end BETWEEN first_day AND last_day) AND (segment = 87) THEN 1 ELSE 0 END AS is_canceled_87, CASE WHEN (subscription_end BETWEEN first_day AND last_day) AND (segment = 30) THEN 1 ELSE 0 END AS is_canceled_30 FROM cross_join ), status_aggrega...

WITH  months  AS   (    SELECT        '2017-01-01'   AS  first_day,       '2017-01-31'   AS  last_day     UNION      SELECT        '2017-02-01'   AS  first_day,       '2017-02-28'   AS  last_day     UNION      SELECT        '2017-03-01'   AS  first_day,       '2017-03-31'   AS  last_day ) ,  cross_join  AS   (    SELECT  *    FROM  subscriptions    CROSS   JOIN  months ) ,  status  AS   (    SELECT       id,      first_day  AS   month ,       CASE        WHEN   ( subscription_start <...

For the numerator, we only want the portion of the customers who cancelled during January: For the denominator, we only want to be considering customers who were active at the beginning of January: When dividing, we need to be sure to multiply by  1.0  to cast the result as a float: SELECT   1.0  * ( SELECT   COUNT ( * ) FROM  subscriptions WHERE  subscription_start <  '2017-01-01' AND   ( subscription_end BETWEEN   '2017-01-01'   AND   '2017-01-31' ) ) / ( SELECT   COUNT ( * ) FROM  subscriptions WHERE  subscription_start <  '2017-01-01' AND   (    ( subscription_end >=  '2017-01-01' )     OR   ( subscription_end  IS   NULL )    ) ) AS  result;

WITH  funnels  AS   (    SELECT   DISTINCT  b.browse_date,      b.user_id,      c.user_id  IS   NOT   NULL   AS   'is_checkout' ,      p.user_id  IS   NOT   NULL   AS   'is_purchase'    FROM  browse  AS   'b'    LEFT   JOIN  checkout  AS   'c'      ON  c.user_id = b.user_id    LEFT   JOIN  purchase  AS   'p'      ON  p.user_id = c.user_id ) SELECT  browse_date, COUNT ( * )   AS   'num_browse' ,     SUM ( is_checkout )   AS   'num_checkout' ,     SUM ( is_purchase )   AS   'num_purchase' ,     1.0  *  SUM ( is_checkout )  /  COUNT ( user_id )   AS   'browse_to_checkout' ...

The  WITH  statement allows us to perform a separate query (such as aggregating customer’s subscriptions) previous_query  is the alias that we will use to reference any columns from the query inside of the  WITH  clause We can then go on to do whatever we want with this temporary table (such as join the temporary table with another table) Essentially, we are putting a whole first query inside the parentheses  ()  and giving it a name. After that, we can use this name as if it’s a table and write a new query  using  the first query. WITH  previous_query  AS ( SELECT  customer_id, COUNT ( subscription_id )   AS   'subscriptions' FROM  orders GROUP   BY  customer_id ) SELECT  customers.customer_name,previous_query.subscriptions FROM  previous_query JOIN  customers ON  previous_query.customer_id = customers.customer_id;

For  strftime(__, timestamp) : %Y  returns the year (YYYY) %m  returns the month (01-12) %d  returns the day of the month (1-31) %H  returns 24-hour clock (00-23) %M  returns the minute (00-59) %S  returns the seconds (00-59

CREATE TABLE  creates a new table. INSERT INTO  adds a new row to a table. SELECT  queries data from a table. ALTER TABLE  changes an existing table. UPDATE  edits a row in a table. DELETE FROM  deletes rows from a table CREATE   TABLE  friends ( id INTEGER, name TEXT, birthday  DATE ) ; INSERT   INTO  friends ( id,name,birthday ) VALUES ( 1 , 'Jane Doe' , '1990-05-30' ) ; INSERT   INTO  friends ( id,name,birthday ) VALUES ( 2 , 'Ali Pansare' , '1998-08-15' ) ; INSERT   INTO  friends ( id,name,birthday ) VALUES ( 3 ,  'Junaid Patel' , '1996-05-22' ) ; UPDATE  friends SET  name =  'Jane Smith' where  id =  1 ; ALTER   TABLE  friends ADD   COLUMN  email TEXT; UPDATE  friends SET  email =  'jane@codecademy.com' where  id =  1 ; UPDATE  friends SET  email =  'ap@gmail.c...