$1.$ Basic Customer Order History & Lifetime Value $($SQL Only$)$

@ P

Lang

MySC

$$ Envil

$1$

$2$

$3$

$4$

$1$

$2$

Fantasy Cosmetics wants to understand their customers' shopping behavior. Write a query that will return the following $4$ values per customer:

$$ customer$\_$name

$$ number$\_$of$\_$invoices: Total number of invoices associated with a customer over all time, regardless of whether the invoice was cancelled or refunded. Return zero if that customer has no invoices.

$$ lifetime$\_$value: This is the sum of how much a customer has spent $("$total $\_$price"$),$ totaled across all invoices. Exclude invoices that were cancelled or refunded. Return zero if that customer has no qualifying invoices. All values should be returned as an unsigned integer.

$$ most$\_$recent$\_$invoice$\_$time: The most recent time an invoice was issued for a customer, regardless of whether it was cancelled or refunded. Return NULL if that customer has no invoices.

This query should return results for all customers, even if they do not have any invoices.

Sort the results based on lifetime value from highest$-$to$-$lowest, then by customer name in case of ties.

Table definitions and a data sample are given below.