Spreadsheet
1. Pull all user data, but only for Canadian users
Filter by values, and only select "Canada". Here's our approach
2. Pull the User ID and Signup Timestamp for any user with an even User ID between 90 and 100
Filter by values, and only select even User IDs between 90 and 100. Here's our approach
3. Pull all user data, but only for Canadian users that deleted their account
Filter by values, "Canada" from Country and "True" from User Deleted. Here's our approach
4. Pull all payment data in 2018, or any payment that's been returned
It's possible to instrument multiple filters with connecting "OR" logic via the Filter feature in spreadsheets. However, it can get complicated, fast.
The next Foundation lesson (Segment) will introduce a cleaner way to accomplish a task like this. In the interim, here's our approach – Filter by condition, and use a Custom Formula to build-out your filter logic.
=OR(YEAR(C:C)=2018, F:F=TRUE)
The formula above basically means, "pull all data where the Year in column C is equal to 2018, or the value in column F is equal to True".
5. Pull all payment data in 2018, or any payment in 2019 >= $500
Again, it's possible to instrument multiple filters with connecting "OR" logic via the Filter feature in spreadsheets. However, it can get complicated, fast.
The next Foundation lesson (Segment) will introduce a cleaner way to accomplish a task like this. In the interim, here's our approach – Filter by condition, and use a Custom Formula to build-out your filter logic.
=OR(YEAR(C:C)=2018, AND(YEAR(C:C)=2019, E:E>=500))
The formula above basically means, "pull all data where the Year in column C is equal to 2018, or the year in column C is equal to 2019 and the value in column D is equal to 2".
Database and SQL
-- 1. Pull all user data, but only for Canadian users
-- 2. Pull the User ID and Signup Timestamp for any user with an even User ID between 90 and 100
-- 3. Pull all user data, but only for Canadian users that deleted their account
-- 4. Pull all payment data in 2018, or any payment that's been returned
-- 5. Pull all payment data in 2018, or any payment in 2019 >= 500
-- 1. Pull all user data, but only for Canadian users
SELECT *
FROM getting_started.users
WHERE country = 'Canada'
;
-- 2. Pull the User ID and Signup Timestamp for any user with an even User ID between 90 and 100
SELECT
user_id
, signup_timestamp
FROM getting_started.users
WHERE user_id IN (90, 92, 94, 96, 98, 100)
;
-- 3. Pull all user data, but only for Canadian users that deleted their account
SELECT *
FROM getting_started.users
WHERE
country = 'Canada'
AND
user_deleted = TRUE
;
-- 4. Pull all payment data in 2018, or any payment that's been returned
SELECT *
FROM getting_started.payments
WHERE
payment_timestamp BETWEEN '2018-01-01' AND '2018-12-31'
OR
payment_returned = TRUE
;
-- 5. Pull all payment data in 2018, or any payment in 2019 >= 500
SELECT *
FROM getting_started.payments
WHERE
payment_timestamp BETWEEN '2018-01-01' AND '2018-12-31'
OR
(
payment_timestamp BETWEEN '2019-01-01' AND '2019-12-31'
AND
payment_amount >= 500
)
;
BI Software
1. Pull all user data, but only for Canadian users
After giving it a shot on your own, here's our approach.
2. Pull the User ID and Signup Timestamp for any user with an even User ID between 90 and 100
After giving it a shot on your own, here's our approach.
3. Pull all user data, but only for Canadian users that deleted their account
After giving it a shot on your own, here's our approach.
4. Pull all payment data in 2018, or any payment that's been returned
After giving it a shot on your own, here's our approach.
5. Pull all payment data in 2018, or any payment in 2019 >= $500
After giving it a shot on your own, here's our approach.