quarterdome

hKRib2R5hqhkZXRhY2hlZMOpaGFzaF90eXBlCqNrZXnEIwEg4ZWsklthU1Aw+1ih4sEzhgEvl7LwDX8LIVtxGDrCl2IKp3BheWxvYWTFA0J7ImJvZHkiOnsia2V5Ijp7ImVsZGVzdF9raWQiOiIwMTIwZTE5NWFjOTI1YjYxNTM1MDMwZmI1OGExZTJjMTMzODYwMTJmOTdiMmYwMGQ3ZjBiMjE1YjcxMTgzYWMyOTc2MjBhIiwiaG9zdCI6ImtleWJhc2UuaW8iLCJraWQiOiIwMTIwZTE5NWFjOTI1YjYxNTM1MDMwZmI1OGExZTJjMTMzODYwMTJmOTdiMmYwMGQ3ZjBiMjE1YjcxMTgzYWMyOTc2MjBhIiwidWlkIjoiNGIyMzg4NDJhNzlmZjMxYjYxMzcwZmQ0NWM5ZDkwMTkiLCJ1c2VybmFtZSI6InF1YXJ0ZXJkb21lIn0sIm1lcmtsZV9yb290Ijp7ImN0aW1lIjoxNTA3MzA4NTQ2LCJoYXNoIjoiYTFhMTAxMjcyNTliNWU2MGEwN2FiMmFlYWIyYzVhNmQyY2FkODZjOTE3OWNjMGI0OWE1YTdkNjAwNmM4MDllOTc3MDBjYTZlMTFlODY2M2YyNzA4Mjk5ZWRiMmVlMTg3YjVhYzgwYTZjN2RiNzRiYTE2ZmQ4N2U5M2FmZjk3MDUiLCJoYXNoX21ldGEiOiI2ZmNmOTJmZTM0ODM1YmU2ODQ3Y2NjZTJhZjI2ZWQ1NzY4N2Q3OWFiNmFkMzVhNDYyMDMyNWE0NTQ4ZWFhMDVjIiwic2Vxbm8iOjE1MjgyNjV9LCJzZXJ2aWNlIjp7Im5hbWUiOiJnaXRodWIiLCJ1c2VybmFtZSI6InF1YXJ0ZXJkb21lIn0sInR5cGUiOiJ3ZWJfc2VydmljZV9iaW5kaW5nIiwidmVyc2lvbiI6MX0sImNsaWVudCI6eyJuYW1lIjoia2V5YmFzZS5pbyBnbyBjbGllbnQiLCJ2ZXJzaW9uIjoiMS4wLjMz

quarterdome

select 1 in (1,2);

select 3 in (1,2);

select 3 not in (1,2);

select null in (1,2,null);

select null not in (1,2,null);

quarterdome

-- This SQL snippet attributes an interest to traffic source and campaign. 
-- Any interest event is attributed to FIRST traffic_source event for that 
-- user. 


with 

-- first order traffic_sources per user, and number them with row_number

ordered_traffic_sources as (

quarterdome

-- This SQL snippet attributes an interest to traffic source and campaign. 
-- Any interest event is attributed to previous traffic_source event. 
--
-- The approach used here, uses 'lag() ignore nulls'. This is supported
-- on Redshift, but not in PostgreSQL. This approach yields a shorter,
-- more readable, and likely faster query, but it is less portable. 

with 

-- Merge traffic_sources and interest events in one event pool. 

quarterdome

-- This SQL snippet attributes an interest to traffic source. Any interest 
-- event is attributed to previous traffic_source event. 

with 

-- Merge traffic_sources and interest events in one event pool. 
--  * pad with 'null' every field that is not common between these two events
--  * add a new field 'is_first_event'; set it to 1 for traffic_source events 
--  and to 0 for interest events
--  * add a new field 'type' with event type: interest or traffic_source.

quarterdome

Analyzing Events - SQL Challenge

Lets assume you have the following tables tracking web app activity in a fully featured SQL database (e.g. PostgreSQL).

identifies 
  user_id
  sent_at
  email
  first_name

quarterdome

with

-- First add a column to tracks table with the timestamp of 
-- previous event (using lag window function). Note, that 
-- lag() will return null if it is first event. 

linked_tracks as (
  select
    *,
    lag(sent_at, 1) over

quarterdome

Welcome to Apartment List code test!

For an apartment search we need to generate description snippets that match the search query. For example, lets assume we have the apartment with the following description:

"Our luxury loft-style apartments were constructed as condominiums, so your new residence will have: Solid floors and walls (this will be the quietest apartment you've EVER lived in); Premium stainless steel designer appliances; Distinctive accent walls and hardwood flooring; A kitchen that most chefs would drool over with easy to clean gas stove and countertops; Walk in closets with built in storage; Full size washer and dryer in each apartment home. In addition, all residents will enjoy use of our top-notch amenities, including reserved parking, cutting-edge fitness center, wireless internet cafe/business center, and rooftop lounge to soak up the sun!"

Lets consider a user searching for "designer kitchen". In this case, a good snippet would be something like this:

"Premium stainless steel designer

quarterdome

#
# lets say we have a LARGE table 

test=# select count(*) from blah;
 count 
----------
 20000000
(1 row)

#