Spreadsheet
1. How many rows of data are in the Getting Started: Users dataset?
Counting the rows of a dataset in Google Sheets can be done a few different ways. The easiest of which is "by eye". As in, scrolling to the bottom of the dataset and looking at the row number of the final row of data.
Another way is to use the COUNTA function, as you can see here in our approach.
2. How many unique users are in the Getting Started: Users dataset?
After giving it a shot on your own, here's our approach to solving this problem with the COUNTUNIQUE function.
3. How many unique users in the Getting Started: Users dataset are from Canada?
After giving it a shot on your own, here's our approach to solving this problem with the COUNTUNIQUEIFS function.
4. What was the timestamp of the first signup? How about the last?
After giving it a shot on your own, here's our approach to solving this problem with the MIN and MAX function.
5. What is the total payment amount in the Getting Started: Payments dataset for all payments <= $250?
After giving it a shot on your own, here's our approach to solving this problem with the SUMIF function.
Database and SQL
-- 1. How many rows of data are in the Getting Started: Users dataset?
-- 2. How many unique users are in the Getting Started: Users dataset?
-- 3. How many unique users in the Getting Started: Users dataset are from Canada?
-- 4. What was the timestamp of the first signup? How about the last?
-- 5. What is the total payment amount in the Getting Started: Payments dataset for all payments <= $250?
-- 1. How many rows of data are in the Getting Started: Users dataset?
SELECT
COUNT(*)
FROM getting_started.users
;
-- 2. How many unique users are in the Getting Started: Users dataset?
SELECT
COUNT(DISTINCT user_id) AS unique_num_users
FROM getting_started.users
;
-- 3. How many unique users in the Getting Started: Users dataset are from Canada?
SELECT
COUNT(DISTINCT user_id) AS unique_num_users
FROM getting_started.users
WHERE country = 'Canada'
;
-- 4. What was the timestamp of the first signup? How about the last?
SELECT
MIN(signup_timestamp) AS min_signup
, MAX(signup_timestamp) AS max_signup
FROM getting_started.users
;
-- 5. What is the total payment amount in the Getting Started: Payments dataset for all payments <= $250?
SELECT
SUM(payment_amount) AS total_payment_amount
FROM getting_started.payments
WHERE payment_amount <= 250
;
BI Software
1. How many rows of data are in the Getting Started: Users dataset?
Looker is typically not used to simply count the number or rows in a dataset, as it will commonly default to de-duplicating data. However, if each row is unique and/or you're aware of the unique field (primary key) in a dataset it can be done. After giving it a shot on your own, here's our approach to solving this problem.
2. How many unique users are in the Getting Started: Users dataset?
After giving it a shot on your own, here's our approach to solving this problem.
In Looker's backend (the LookML code), we've specified that user_id is a unique field (primary key) in the Getting Started: Users dataset. For this reason, Looker will extract unique User IDs automatically (as the field is inherently unique).
3. How many unique users in the Getting Started: Users dataset are from Canada?
After giving it a shot on your own, here's our approach to solving this problem.
4. What was the timestamp of the first signup? How about the last?
In using a visualization feature in Looker (Limit Displayed Rows), we can show just a single row of data. From here we can click back-and-forth on the Signup Time field to grab the first and last signup.
After giving it a shot on your own, here's our approach to solving this problem.
5. What is the total payment amount in the Getting Started: Payments dataset for all payments <= $250?
After giving it a shot on your own, here's our approach to solving this problem.