![[SQL_for_Data_Analytics_Perform_Fast_and_Efficient_....pdf]]
## coalese
> To illustrate a simple usage of the COALESCE function, let's return to the customers table. Let's say the marketing team would like a list of the first names, last names, and phone numbers of all male customers. However, for those customers with no phone number, they would like the table to instead write the value 'NO PHONE'. We can accomplish this request with `COALESCE`:
```sql
SELECT first_name
, last_name
, COALESCE(phone, 'NO PHONE') AS phone
FROM customers
ORDER BY 1;
```
> When dealing with creating default values and avoiding `NULL`, `COALESCE` will always be helpful.
## Least Greatest
Unlike aggregate functions such as `MIN` or `MAX` using `LEAST` or `GREATEST` seems to be scalar oriented
> Two functions that come in handy for data preparation are the `LEAST` and `GREATEST` functions. Each function takes any number of values and returns the least or the greatest of the values, respectively.
>
> A simple use of this variable would be to replace the value if it's too high or low. For example, the sales team may want to create a sales list where every scooter is $600 or less than that. We can create this using the following query:
```sql
SELECT product_id
, model
, year
, product_type
, LEAST(600.00, base_msrp) AS base_msrp
, production_start_date
, production_end_date
FROM products
WHERE product_type='scooter'
ORDER BY 1;
```
## Window Functions
Examples of window functions
```sql
SELECT {columns}
, {window_func} OVER (PARTITION BY {partition_key} ORDER BY {order_key})
FROM table1;
```
more realistic example:
```sql
SELECT customer_id
, title
, first_name
, last_name
, gender
, COUNT(*) OVER (PARTITION BY gender ORDER BY customer_id) AS total_customers
, SUM(CASE WHEN title IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY gender ORDER BY customer_id) AS total_customers_title
FROM customers
ORDER BY customer_id;
```
The window Keyword:
```sql
SELECT customer_id
, title
, first_name
, last_name
, gender
, COUNT(*) OVER w AS total_customers,
, SUM(CASE WHEN title IS NOT NULL THEN 1 ELSE 0 END) OVER w AS total_customers_title
FROM customers
WINDOW w AS (PARTITION BY gender ORDER BY customer_id) -- Reduces typing and improves legibility
ORDER BY customer_id;
```