\textbf{Feature:} This query is used to find out the country-wise trends in the number of orders and revenue. \\
\textbf{Location:} In function \texttt{admin\_stats(page)} in \texttt{front-end/\_\_init\_\_.py}

\textbf{Usage:}
The query can be extended to rollup with states, cities, etc. by adding them to the \texttt{SELECT} and \texttt{GROUP BY} clause.

\begin{lstlisting}
SELECT
    a.country,
    COUNT(DISTINCT o.orderID) AS order_count,
    SUM(op.quantity * p.price) AS revenue
FROM orders o
JOIN order_product op ON o.orderID = op.orderID
JOIN product p ON op.productID = p.productID
JOIN customer c ON o.customerID = c.customerID
JOIN address a ON c.addressID = a.addressID
GROUP BY a.country WITH ROLLUP
ORDER BY revenue DESC;
\end{lstlisting}