mysql> select count(*) as numTransactions from OnlineRetail;
+-----------------+
| numTransactions |
+-----------------+
|          541909 |
+-----------------+
1 row in set (0.23 sec)


mysql> select sum(UnitPrice * Quantity) as revenue from OnlineRetail;
+------------+
| revenue    |
+------------+
| 9557741.00 |
+------------+
1 row in set (0.50 sec)

mysql> select count(distinct InvoiceNo) as visits from OnlineRetail;
+--------+
| visits |
+--------+
|  22062 |
+--------+
1 row in set (0.43 sec)

mysql> select count(distinct StockCode) as numItems from OnlineRetail;
+----------+
| numItems |
+----------+
|     3940 |
+----------+
1 row in set (0.84 sec)

mysql> select weekday(str_to_date(InvoiceDate, '%m/%d/%Y')) as day, sum(UnitPrice * Quantity) as revenue from OnlineRetail group by day;
+-------+------------+
| day   | revenue    |
+-------+------------+
|     0 | 1590466.15 |
|     1 | 1922892.70 |
|     2 | 1710684.77 |
|     3 | 2046346.53 |
|     4 | 1499740.50 |
|     6 |  787610.35 |
+-------+------------+
6 rows in set, 65535 warnings (1.04 sec)

mysql> select dayname(str_to_date(InvoiceDate, '%m/%d/%Y')) as day, sum(UnitPrice * Quantity) as revenue from OnlineRetail group by day;
+-----------+------------+
| day     | revenue    |
+-----------+------------+
| Friday    | 1499740.50 |
| Monday    | 1590466.15 |
| Sunday    |  787610.35 |
| Thursday  | 2046346.53 |
| Tuesday   | 1922892.70 |
| Wednesday | 1710684.77 |
+-----------+------------+
6 rows in set, 65535 warnings (1.45 sec)

mysql> select CustomerID, sum(UnitPrice * Quantity) as revenue from OnlineRetail where CustomerID > 0 group by CustomerID order by revenue desc limit 0,10;
+------------+-----------+
| CustomerID | revenue   |
+------------+-----------+
|      14646 | 276830.19 |
|      18102 | 251594.32 |
|      17450 | 189147.03 |
|      14911 | 127250.08 |
|      12415 | 122658.74 |
|      14156 | 110627.11 |
|      17511 |  88013.48 |
|      16684 |  65581.33 |
|      13694 |  62785.56 |
|      15311 |  59364.14 |
+------------+-----------+
10 rows in set (0.68 sec)

mysql> select StockCode, sum(UnitPrice * Quantity) as revenue from OnlineRetail group by StockCode order by revenue desc limit 0,10;
+-----------+-----------+
| StockCode | revenue   |
+-----------+-----------+
| 22423     | 129809.63 |
| 85123A    |  99846.98 |
| 47566     |  96093.38 |
| 85099B    |  92356.03 |
| 23084     |  66756.59 |
| 22086     |  63791.94 |
| 84879     |  58959.73 |
| 79321     |  52363.99 |
| 22197     |  50987.47 |
| 23298     |  42206.42 |
+-----------+-----------+
10 rows in set (1.08 sec)

mysql> select Description, sum(UnitPrice * Quantity) as revenue from OnlineRetail group by Description order by revenue desc limit 0,10;
+------------------------------------+-----------+
| Description                        | revenue   |
+------------------------------------+-----------+
| REGENCY CAKESTAND 3 TIER           | 129809.63 |
| WHITE HANGING HEART T-LIGHT HOLDER |  99668.47 |
| PARTY BUNTING                      |  96093.38 |
| JUMBO BAG RED RETROSPOT            |  92356.03 |
| RABBIT NIGHT LIGHT                 |  66756.59 |
| PAPER CHAIN KIT 50'S CHRISTMAS     |  63791.94 |
| ASSORTED COLOUR BIRD ORNAMENT      |  58959.73 |
| CHILLI LIGHTS                      |  52363.99 |
| JUMBO BAG PINK POLKADOT            |  41619.66 |
| SPOTTY BUNTING                     |  41571.72 |
+------------------------------------+-----------+
10 rows in set (1.61 sec)

create table customerVisitRevenue as
select CustomerID, count(distinct InvoiceNo) as visits, sum(UnitPrice * Quantity) as revenue
from OnlineRetail
where CustomerID > 0
group by CustomerID;