<jupyter_start><jupyter_text>Show average price for each group<jupyter_code>query = """
SELECT product_groups.group_name, AVG (products.price)
FROM  products
INNER JOIN product_groups on products.group_id = product_groups.group_id
GROUP BY product_groups.group_name;
"""
# INNER JOIN product_groups USING (group_id)
ans = pd.read_sql_query(query, con=engine)
ans<jupyter_output><empty_output><jupyter_text>Window Function Syntaxwindow_function(arg1, arg2,..) OVER (PARTITION BY expression ORDER BY expression)  Query returns the product name, the price, product group name, along with the average prices of each product group.<jupyter_code>query = """
select product_name, price, group_name, avg(price) over (partition by group_name)
from products
inner join product_groups using (group_id);
"""
ans = pd.read_sql_query(query, con=engine)
ans<jupyter_output><empty_output><jupyter_text>The ROW_NUMBER() function assigns a running serial number to rows in each partition. See the following query:<jupyter_code>query = """
SELECT  product_name, group_name, price, ROW_NUMBER () OVER ( PARTITION BY group_name ORDER BY price )
FROM products
INNER JOIN product_groups USING (group_id);
"""
ans = pd.read_sql_query(query, con=engine)
ans<jupyter_output><empty_output><jupyter_text>The DENSE_RANK() function assigns a running serial number to rows in each partition. See the following query:<jupyter_code>query = """
SELECT  product_name, group_name, price, DENSE_RANK () OVER ( PARTITION BY group_name ORDER BY price )
FROM products
INNER JOIN product_groups USING (group_id);
"""
ans = pd.read_sql_query(query, con=engine)
ans<jupyter_output><empty_output><jupyter_text>Select top 2 per group based on the least price<jupyter_code>query = """
select * from 
    ( SELECT  product_name, group_name, price, RANK () OVER ( PARTITION BY group_name ORDER BY price ) as rank
    FROM products
    INNER JOIN product_groups USING (group_id)
    ) as foo
where foo.rank <=3;
"""
ans = pd.read_sql_query(query, con=engine)
ans
query1 = """
    DROP TABLE product_groups2;
    CREATE TABLE product_groups2 (
    group_id serial PRIMARY KEY,
    group_name VARCHAR (255) NOT NULL
    );
"""
query2 = """
    DROP TABLE products2;
    CREATE TABLE products2 (
    product_id serial PRIMARY KEY,
    product_name VARCHAR (255) NOT NULL,
    price DECIMAL (11, 2),
    group_id INT NOT NULL,
    sell_time TIMESTAMP,
    FOREIGN KEY (group_id) REFERENCES product_groups (group_id)
    );

    INSERT INTO product_groups2 (group_name)
    VALUES
    ('Smartphone'),
    ('Laptop'),
    ('Tablet');

    INSERT INTO products2 (product_name, group_id,price, sell_time)
    VALUES
    ('Microsoft Lumia', 1, 200, '2019-04-11 08:50:16'),
    ('HTC One', 2, 400, '2019-04-11 08:49:14'),
    ('Nexus', 1, 500, '2019-04-11 08:49:16'),
    ('iPhone', 1, 900, '2019-04-11 08:51:16'),
    ('HP Elite', 2, 1200, '2019-04-11 08:50:26'),
    ('Lenovo Thinkpad', 2, 700, '2019-04-11 08:48:16'),
    ('Sony VAIO', 2, 700, '2019-04-11 08:48:16'),
    ('Dell Vostro', 2, 800, '2019-04-11 08:50:26'),
    ('iPad', 3, 700, '2019-04-11 08:49:16'),
    ('Kindle Fire', 3, 150, '2019-04-11 08:51:16'),
    ('Samsung Galaxy Tab', 3, 200, '2019-04-11 08:50:16');
 """
# engine
engine.execute(query1)
engine.execute(query2)
pd.read_sql_table("products2", con=engine).head(20)
query = """ select *,date_trunc('minute', sell_time) from products2;"""
ans = pd.read_sql_query(query, con=engine)
ans
query = """ 
select group_id, min(price), max(price), avg(price), date_trunc('minute', sell_time) as date_minute 
from products2
group by group_id, date_minute
order by date_minute; 
"""
ans = pd.read_sql_query(query, con=engine)
ans
products2 = pd.read_sql_table("products2", con=engine)
products2 = products2.drop('product_id',axis=1)
products2
file_number = 1
start = 1
end = 3
fileName = "./batch_data2/batch_" + str(file_number) + ".csv"
# with open(fileName, "w") as f:
df = products2.iloc[start:end, :]
df.to_csv(fileName, index=False)
start = end
end = end + 3
file_number = file_number + 1<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12870801667075263, 'fraction_numerical': 0.05883795047805835, 'mean_word_length': 5.071428571428571, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 21, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '452550', 'license': 'MIT', 'n_tokens_mistral': 1607, 'n_tokens_neox': 1409, 'n_words': 396, 'path': 'notebook/window function based SQL queries.ipynb', 'repo_name': 'sdadia/internship', 'size': '4079'}