One Question, Three Stores

The shop wants to show its 3 best-selling products on every page. In this lesson you ask three stores the same question: SQLite's shop.db, MongoDB and Redis. All three must name the same products. How each one gets there shows what it is good at.

SQL and MongoDB count when you ask. In SQL, GROUP BY makes one group per product, so SUM adds up each product's quantities. In MongoDB the items sit inside each order. So $unwind first makes one document per item, and then $group does the job of GROUP BY. Here is the number of orders per status, both ways:

# SQL: SELECT status, COUNT(*) AS orders FROM orders GROUP BY status
pipeline = [{"$group": {"_id": "$status", "orders": {"$sum": 1}}}]

Redis counts as things happen. A sorted set keeps members in order of their score. r.zincrby(key, amount, member) adds to one member's score. r.zrevrange(key, 0, 2, withscores=True) gives back the top 3, highest score first. Here it counts page views as they happen:

r.zincrby("views", 1, "Lamp")
print(r.zrevrange("views", 0, 4, withscores=True))

Your script must print the lines below. The check then adds an order of 6 Teapots and asks all three again: each must now give Pen 15, Teapot 8, Mug 7.

SQL: [('Pen', 15), ('Mug', 7), ('Notebook', 5)]
MongoDB: [('Pen', 15), ('Mug', 7), ('Notebook', 5)]
Redis: [('Pen', 15.0), ('Mug', 7.0), ('Notebook', 5.0)]
Best fit: Redis, it keeps the top 3 ready instead of counting every time

Your task: add the GROUP BY to the SQL query, and add the $unwind and $group stages to the pipeline. Then write record_sale so each sale adds to the "best_sellers" sorted set, print the Redis top 3, and print which store fits best and why.

Assignment
Follow the Coding Tutorial to get the same top 3 from SQL, MongoDB and Redis.

Hint
Put $unwind before $group. After it, each document's items holds a single item, so "$items.product" and "$items.quantity" each point at one value. Redis scores are floats, so 15 prints as 15.0.