Spreadsheet
1. Pull the list of every transaction ordered from the oldest to the most recent
Go to the Payments tab, and select cells from B3 - F27. Click on Data --> Sort range --> Sort by Column C, A--> Z .
2. Pull the list of users ordered from the latest to the oldest to the most recent
Go to the Users tab, and select cells from B3 - F102. Click on Data --> Sort range --> Sort by Column B, Z--> A . .
3. Display a random set of 10 users
When clicking on a tab in Google Sheets, the entire data set will be loaded and displayed, so we don't have the concept of a limit in the same way as we have in SQL. However, Google Sheets supports hiding rows from your data, so we can limit what we see after hiding everything but a sample of our dataset.
To display 10 rows from the Users dataset, highlight rows 13 - 102, right click and the select Hide rows 13 - 102.
4. Display the first 5 transactions in the payments dataset
After giving it a shot, here's our approach to solving this problem with a combination of Sort and Hide rows.
5. Pull the top 3 payment amounts
After giving it a shot, here's our approach to solving this problem with a combination of Sort and Hide rows.
Database and SQL
-- 1. Pull the list of every transaction ordered from the oldest to the most recent
-- 2. Pull the list of users ordered from the oldest to the most recent
-- 3. Display a random set of 10 users
-- 4. Display the first 5 transactions in the payments dataset
-- 5. Pull the top 3 payment amounts
-- 1. Pull the list of every transaction ordered from the oldest to most recent
SELECT *
FROM getting_started.payments
ORDER BY payment_timestamp ASC
;
-- 2. Pull the list of users ordered from the oldest to the most recent to signup
SELECT *
FROM getting_started.users
ORDER BY signup_timestamp DESC
;
-- 3. Display a random set of 10 users
SELECT *
FROM getting_started.users
LIMIT 10
;
-- 4. Display the first 5 transactions in the payments dataset
SELECT *
FROM getting_started.payments
ORDER BY payment_timestamp ASC
LIMIT 5
;
-- 5. Pull the top 3 payment amounts
SELECT *
FROM getting_started.payments
ORDER BY payment_amount DESC
LIMIT 3
;
BI Software
1. Pull the list of every transaction ordered from the oldest to the most recent
Your result should look like the table below. After giving it a shot on your own, here's our approach.
2. Pull the list of users ordered from the latest to the oldest to the most recent
Your result should look like the table below. After giving it a shot on your own, here's our approach.
3. Display a random set of 10 users
Your result should look like the table below. After giving it a shot on your own, here's our approach.
4. Display the first 5 transactions in the payments dataset
Your result should look like the table below. After giving it a shot on your own, here's our approach.
5. Pull the top 3 payment amounts
Your result should look like the table below. After giving it a shot on your own, here's our approach.