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
hKRib2R5hqhkZXRhY2hlZMOpaGFzaF90eXBlCqNrZXnEIwEg4ZWsklthU1Aw+1ih4sEzhgEvl7LwDX8LIVtxGDrCl2IKp3BheWxvYWTFA0J7ImJvZHkiOnsia2V5Ijp7ImVsZGVzdF9raWQiOiIwMTIwZTE5NWFjOTI1YjYxNTM1MDMwZmI1OGExZTJjMTMzODYwMTJmOTdiMmYwMGQ3ZjBiMjE1YjcxMTgzYWMyOTc2MjBhIiwiaG9zdCI6ImtleWJhc2UuaW8iLCJraWQiOiIwMTIwZTE5NWFjOTI1YjYxNTM1MDMwZmI1OGExZTJjMTMzODYwMTJmOTdiMmYwMGQ3ZjBiMjE1YjcxMTgzYWMyOTc2MjBhIiwidWlkIjoiNGIyMzg4NDJhNzlmZjMxYjYxMzcwZmQ0NWM5ZDkwMTkiLCJ1c2VybmFtZSI6InF1YXJ0ZXJkb21lIn0sIm1lcmtsZV9yb290Ijp7ImN0aW1lIjoxNTA3MzA4NTQ2LCJoYXNoIjoiYTFhMTAxMjcyNTliNWU2MGEwN2FiMmFlYWIyYzVhNmQyY2FkODZjOTE3OWNjMGI0OWE1YTdkNjAwNmM4MDllOTc3MDBjYTZlMTFlODY2M2YyNzA4Mjk5ZWRiMmVlMTg3YjVhYzgwYTZjN2RiNzRiYTE2ZmQ4N2U5M2FmZjk3MDUiLCJoYXNoX21ldGEiOiI2ZmNmOTJmZTM0ODM1YmU2ODQ3Y2NjZTJhZjI2ZWQ1NzY4N2Q3OWFiNmFkMzVhNDYyMDMyNWE0NTQ4ZWFhMDVjIiwic2Vxbm8iOjE1MjgyNjV9LCJzZXJ2aWNlIjp7Im5hbWUiOiJnaXRodWIiLCJ1c2VybmFtZSI6InF1YXJ0ZXJkb21lIn0sInR5cGUiOiJ3ZWJfc2VydmljZV9iaW5kaW5nIiwidmVyc2lvbiI6MX0sImNsaWVudCI6eyJuYW1lIjoia2V5YmFzZS5pbyBnbyBjbGllbnQiLCJ2ZXJzaW9uIjoiMS4wLjMz |
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); |
-- 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 ( |
-- 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. |
-- 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. |
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 |
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 |
# | |
# lets say we have a LARGE table | |
test=# select count(*) from blah; | |
count | |
---------- | |
20000000 | |
(1 row) | |
# |