\textbf{Feature:} This query is used to retrieve the demographics of supplier activity. \\
\textbf{Location:} In function \texttt{admin\_stats(page)} in \texttt{front-end/\_\_init\_\_.py}

\textbf{Usage:}
The OLAP data contains the number of suppliers that are selling products on EBMS grouped by demographics.
The average earning per order along with total earning is also shown.
This query is analogous to the query retrieving customer demographics.

\begin{lstlisting}
SELECT
    country, state,
    COUNT(DISTINCT supplier.supplierID) AS supplier_count,
    AVG(product.price * order_product.quantity) AS avg_earned,
    SUM(product.price * order_product.quantity) AS total_earned
FROM supplier
JOIN orders ON supplier.supplierID IN (
    SELECT supplierID FROM product WHERE productID IN (
        SELECT productID FROM order_product WHERE orderID = orders.orderID
    )
)
JOIN order_product ON orders.orderID = order_product.orderID
JOIN product ON order_product.productID = product.productID
JOIN address ON supplier.addressID = address.addressID
GROUP BY country, state WITH ROLLUP
ORDER BY country ASC, total_earned DESC;
\end{lstlisting}