Hands_On.sql

--Show Meta data tables

--Distributed tables.
SELECT nodes,partmethod,colocationid FROM pg_dist_partition;

--Shards for the stores table
SELECT logicalrelid,shardid,shardminvalue,shardmaxvalue from pg_dist_shard where logicalrelid='stores'::regclass;

--Shard placements
SELECT shardid,nodename FROM pg_dist_shard_placement;

-- Total number of stores
SELECT count(*) from stores;

--Explain the above query
EXPLAIN SELECT count(*) from stores;


--List the products for a particular store
SELECT id,
       name,
       price
FROM products
WHERE store_id = foo;

--Explain Analyze the above query
EXPLAIN ANALYZE SELECT id,
       name,
       price
FROM products
WHERE store_id = foo;


-- Create a new order for a particular store.
INSERT INTO orders VALUES();

-- Update the shipping address for a particular order of a store.
UPDATE orders SET shipping_address='', updated_at=now() WHERE order_id='' AND store_id='';

--Explain the above UPDATE query
EXPLAIN UPDATE orders SET shipping_address='', updated_at=now() WHERE order_id='' AND store_id='';


--Two-way join
--Total number of iphones ordered in the month of july for a particular store
--Approach #1 - join on product_id, store_id and place a filter for store_id on only one of the table.
SELECT sum(l.quantity) from
line_items l INNER JOIN products p ON l.product_id=p.id AND l.store_id=p.store_id
WHERE p.name='iphone' AND l.created_at>='' AND L.created_at<=''
AND l.store_id=''

--Approach #2 - join on just the product_id and place filters for store_id on both the  tables.

SELECT sum(l.quantity) from
line_items l INNER JOIN products p ON l.product_id=p.id
WHERE p.name='iphone' AND l.created_at>='' AND L.created_at<=''
AND l.store_id='' and p.store_id=''

--Explain the above query
EXPLAIN SELECT sum(l.quantity) from
line_items l INNER JOIN products p ON l.product_id=p.id
WHERE p.name='iphone' AND l.created_at>='' AND L.created_at<=''
AND l.store_id='' and p.store_id=''


--Three way join
--Top 10 revenue generating products in a given zip code for a particular store.
SELECT p.name, SUM(l.total_price) revenue
FROM line_items l, products p, orders o
where l.product_id=p.id and l.order_id=orders.id
and o.shipping_address=''
and l.store_id='' and p.store_id='' and  o.store_id=''
GROUP BY p.name
ORDER BY revenue DESC LIMIT 10;

--Advance Query