**Spreadsheet**

**1. What is the total payment amount that has been, and has not been returned?**

After giving it a shot on your own, here's our approach to solving this problem.

**2. How many users in each country have, and have not deleted their account?**

After giving it a shot on your own, here's our approach to solving this problem.

**3. How many users have signed up in each month?**

After giving it a shot on your own, here's our approach to solving this problem. We added a new field (Signup Month) that uses the **LEFT** function to extract only the month from our Signup Timestamp.

**4. By month, how many payments were <= $500, and how many payments were > $500?**

After giving it a shot on your own, here's our approach to solving this problem with. Our solution includes two new fields, using the **LEFT** and **IF** function in Google Sheets.

**5. By year, how many United States users have, and have not deleted their account?**

After giving it a shot on your own, here's our approach to solving this problem with. Our solution includes one new field, and uses the **LEFT** function in Google Sheets.

**Database and SQL**

```
-- 1. What is the total payment amount that has been, and has not been returned?
-- 2. How many users in each country have, and have not deleted their account?
-- 3. How many users have signed up in each month?
-- 4. By month, how many payments were <= $500, and how many payments were > $500?
-- 5. By year, how many United States users have, and have not deleted their account?
```

```
-- 1. What is the total payment amount that has been, and has not been returned?
SELECT
payment_returned
, SUM(payment_amount) AS sum_payment
FROM getting_started.payments
GROUP BY payment_returned
;
-- 2. How many users in each country have, and have not deleted their account?
SELECT
country
, user_deleted
, COUNT(DISTINCT user_id) AS num_unique_user
FROM getting_started.users
GROUP BY 1,2
;
-- 3. How many users have signed up in each month?
SELECT
DATE_TRUNC('MONTH', signup_timestamp) AS month
, COUNT(DISTINCT user_id) AS num_unique_user
FROM getting_started.users
GROUP BY 1
ORDER BY 1 ASC
;
-- 4. By month, how many payments were <= $500, and how many payments were > $500?
SELECT
DATE_TRUNC('MONTH', payment_timestamp) AS month
, COUNT(CASE WHEN payment_amount <= 500 THEN 1 ELSE NULL END) AS num_payment_to_500
, COUNT(CASE WHEN payment_amount > 500 THEN 1 ELSE NULL END) AS num_payment_over_500
, COUNT(DISTINCT payment_id) AS total_num_payments
FROM getting_started.payments
GROUP BY 1
ORDER BY 1 ASC
;
-- 5. By year, how many United States users have, and have not deleted their account?
SELECT
DATE_TRUNC('YEAR', signup_timestamp) AS year
, COUNT(DISTINCT CASE WHEN country = 'United States' AND user_deleted = TRUE THEN user_id ELSE NULL END) AS num_unique_user_usa_deleted
, COUNT(DISTINCT CASE WHEN country = 'United States' AND user_deleted = FALSE THEN user_id ELSE NULL END) AS num_unique_user_usa_not_deleted
FROM getting_started.users
GROUP BY 1
ORDER BY 1 ASC
;
```

**BI Software**

**1. What is the total payment amount that has been, and has not been returned?**

After giving it a shot on your own, here's our approach to solving this problem.

**2. How many users in each country have, and have not deleted their account?**

After giving it a shot on your own, here's our approach to solving this problem.

**3. How many users have signed up in each month?**

After giving it a shot on your own, here's our approach to solving this problem.

**4. By month, how many payments were <= $500, and how many payments were > $500?**With the originally created dimensions and measures in Looker this question is most easily answered via the build-out of two reports. One for <= $500, and the other for > $500.

This question is a great example of where we could add more to the back-end of Looker. Specifically, we could add a new dimension built specifically to segment payment amounts by our criteria (i.e. <= $500, or > $500).

For example, if we temporarily add-in a new dimension to Looker containing the logic above, we could then very easily pivot by this dimension and build a single report to reach our answer:

The above is a great example of where an Analyst would spend their time (i.e. developing logic and surfacing new dimensions in BI Software).

**5. By year, how many United States users have, and have not deleted their account?**After giving it a shot on your own, here's our approach to solving this problem.