Singlestore Now 2024 Raffle

1
!pip install pymongo pandas ipywidgets --quiet

1
shared_tier_check = %sql show variables like 'is_shared_tier'
2

3
if shared_tier_check and shared_tier_check[0][1] == 'ON':
4
    current_database = %sql SELECT DATABASE() as CurrentDatabase
5
    database_to_use = current_database[0][0]
6
else:
7
    database_to_use = "new_transactions"
8
    %sql CREATE DATABASE {{database_to_use}}

1
import os
2
import time
3
import numpy as np
4
import pandas as pd
5
import pymongo
6
from pymongo import MongoClient

1
# No need to edit anything
2
myclientmongodb = pymongo.MongoClient("mongodb+srv://mongo_sample_reader:SingleStoreRocks27017@cluster1.tfutgo0.mongodb.net/?retryWrites=true&w=majority")
3
mydbmongodb = myclientmongodb["new_transactions"]
4
mongoitems = mydbmongodb["items"]
5
mongocusts = mydbmongodb["custs"]
6
mongotxs = mydbmongodb["txs"]

1
db_to_use = database_to_use
2
s2clientmongodb = pymongo.MongoClient(connection_url_kai)
3
s2dbmongodb = s2clientmongodb[db_to_use]
4
s2mongoitems = s2dbmongodb["items"]
5
s2mongocusts = s2dbmongodb["custs"]
6
s2mongotxs = s2dbmongodb["txs"]

1
mongocollections = [mongoitems, mongocusts, mongotxs]
2

3
for mongo_collection in mongocollections:
4
    df = pd.DataFrame(list(mongo_collection.find())).reset_index(drop=True)
5
    data_dict = df.to_dict(orient='records')
6
    s2mongo_collection = s2dbmongodb[mongo_collection.name]
7
    s2mongo_collection.insert_many(data_dict)

1
num_iterations = 10
2
mongo_times = []
3

4
# Updated pipeline for total quantity of products sold across all products
5
pipeline = [
6
    {"$group": {"_id": None, "totalQuantity": {"$sum": "$item.quantity"}}}
7
]
8

9
# Simulating same for s2mongoitems
10
s2_times = []
11
for i in range(num_iterations):
12
    s2_start_time = time.time()
13
    s2_result = s2mongoitems.aggregate(pipeline)
14
    s2_stop_time = time.time()
15
    s2_times.append(s2_stop_time - s2_start_time)
16

17
# Retrieving total quantity from the result
18
total_quantity = next(s2_result)["totalQuantity"] if s2_result else 0
19

20
# Returning the numeric values of total quantity sold
21
print("Total Product Quantity Sold is",total_quantity)

1
# Updated pipeline to return the #1 selling product based on total quantity sold
2
pipeline = [
3
    {"$group": {
4
        "_id": "$item.name",  # Group by product name
5
        "total_quantity_sold": {"$sum": "$item.quantity"}  # Sum of quantities sold
6
    }},
7
    {"$sort": {"total_quantity_sold": -1}},  # Sort by total quantity sold in descending order
8
    {"$limit": 1}  # Limit to the top product
9
]
10

11
s2_result = s2mongoitems.aggregate(pipeline)
12

13
# Retrieve the name of the #1 selling product
14
top_product = next(s2_result, None)
15
if top_product:
16
    product_name = top_product["_id"]
17
    total_quantity_sold = top_product["total_quantity_sold"]
18
else:
19
    product_name = "No Data"
20
    total_quantity_sold = 0
21

22
# Return the #1 selling product and its total quantity sold
23
print("Top-Selling product : ",product_name,"With total quantity sold ",total_quantity_sold)

1
# Updated pipeline to exclude "Online" and get top-selling location
2
pipeline = [
3
    {"$lookup":
4
         {
5
          "from": "custs",
6
          "localField": "customer.email",
7
          "foreignField": "email",
8
          "as": "transaction_links",
9
         }
10
    },
11
    {"$match": {"store_location": {"$ne": "Online"}}},  # Exclude Online location
12
    {"$limit": 100},
13
    {"$group":
14
        {
15
         "_id": {"location": "$store_location"},
16
         "count": {"$sum": 1}
17
        }
18
    },
19
    {"$sort": {"count": -1}},
20
    {"$limit": 1}
21
]
22

23

24
s2_result = s2mongotxs.aggregate(pipeline)
25

26

27
# Retrieve the top-selling location excluding "Online"
28
top_location = next(s2_result, None)
29
if top_location:
30
    location_name = top_location["_id"]["location"]
31
    transaction_count = top_location["count"]
32
else:
33
    location_name = "No Data"
34
    transaction_count = 0
35

36
# Return the top-selling location and transaction count
37

38
print("Top-Selling Location : ",location_name,"With transaction of Count ",transaction_count)

1
shared_tier_check = %sql show variables like 'is_shared_tier'
2
if not shared_tier_check or shared_tier_check[0][1] == 'OFF':
3
    %sql DROP DATABASE IF EXISTS new_transactions;