Напредни извештаи од базата (SQL и складирани процедури)

Листа на продавници кои ги имаат на понуда сите продукти од даден производител

   select
	q1.manufacturername, q2.storename, 
	q1.num_products_per_manfucturer, 
	q2.num_products_in_store_per_manufacturer
   from
	(
		select
			m.manufacturerid,
                        m.manufacturername,
                        count(*) as num_products_per_manfucturer
		from
			project.manufacturers as m
			inner join project.products as p on (p.manufacturerid = m.manufacturerid)
		group by
                        m.manufacturerid,
                        m.manufacturername
	) as q1
	
	inner join 

	(
		select
			s.storename,
                        m.manufacturerid,
			count(distinct p.productid) as num_products_in_store_per_manufacturer
		from
			project.stores as s
			inner join project.productinstances as p on (p.storeid = s.storeid)
			inner join project.products as p2 on (p2.productid = p.productid)
			inner join project.manufacturers as m on (m.manufacturerid = p2.manufacturerid)
		group by
                        s.storename,
                        m.manufacturerid
	) as q2
	
	on (q1.manufacturerid = q2.manufacturerid)
   where
	(q1.num_products_per_manfucturer = q2.num_products_in_store_per_manufacturer)
   order by
        q1.manufacturername

Продавница која ги нуди истите продукти како дадената продавница

Функцијата го наоѓа бројот на продукти кои ги нуди дадена продавница

       create or replace function project.get_store_product_count(storeid_var integer)
	returns integer
	
	language plpgsql
	
	as
	
	$$
	declare
		store_product_count integer;
	begin
		select
			count(distinct p.productid) as num_products
		into
			store_product_count
		from
			project.productinstances as p
		where
			p.storeid = storeid_var;
			
		return store_product_count;
	end;
	$$;


       select
	   s.storename,
           q4.num_products
       from
	(
		select
			q3.storeid,
			count(*) as num_products
		from
		(	
			select distinct
				p.storeid,
				p.productid as productid_1
			from
			        project.productinstances as p
			where
				p.storeid = 1
		) as q2
		
		inner join
		
		(
			select distinct
				p.storeid,
				p.productid as productid_2
			from
				project.productinstances as p
			where
				p.storeid != 1
		
		) as q3 
		on (q3.productid_2 = q2.productid_1)
		group by
			q3.storeid
		having
		        (count(*) = project.get_store_product_count(1)) 
		
	) as q4
	
	inner join project.stores as s on (q4.storeid = s.storeid)
	

Продукти со најголем просечен рејтинг на ниво на категорија, додадени во последната година (продуктите се групирани по категорија)

   select
	q2.categoryname,
        q2.productname,
        q2.avg_rating
   from
	(
		select
		        q1.categoryname,
                        q1.productname,
                        q1.avg_rating,
			row_number () over
			(
				partition by
                                      q1.categoryname
				order by
                                      q1.avg_rating desc
			) as rank_num
		from
		(
			select
				c.categoryname,
				p.productname, 
				avg(r.ratingvalue) as avg_rating
			from
				project.products as p
				inner join project.ratings as r on (r.productid = p.productid)
				inner join project.categories as c on (c.categoryid = p.categoryid)
			where
				p.postdate between current_date - interval '1 year' and current_date
			group by
                              c.categoryname,
                              p.productname
			order by
                              avg_rating desc
		) as q1
	
	) as q2
   where
	q2.rank_num = 1

Број на коментари и просечен рејтинг за секој продукт од секој производител (продуктите се сортирани по просечен рејтинг)

   select
	m.manufacturername,
	p.productname,
	(
		select count(*)
		from
			project.usercomments u
		where
			u.productid = p.productid
	
	) as num_comments,
	
	(
		select avg(r.ratingvalue)
		from
			project.ratings r
		where 
			r.productid  = p.productid
	
	) as avg_rating
    from
	project.manufacturers m
	inner join project.products p on (p.manufacturerid = m.manufacturerid)
    order by
          avg_rating desc,
          num_comments desc

Продукт кој му е омилен на најмногу корисници, по категории

   select
	q2.categoryname,
        q2.productname,
        q2.num_users_favourite
   from
	(
		select
			c.categoryname,
                        q1.productid,
                        p.productname,
                        q1.num_users_favourite,
			row_number () over
			(
				partition by
                                      c.categoryid 
				order by 
                                      q1.num_users_favourite desc
			) as rank_num
		from
			 (
			 	select
			 		uf.productid as productid, count(*) as num_users_favourite
			 	from
			 		project.userfavourites as uf
				group by
					uf.productid
				order by
					num_users_favourite desc
			 ) as q1
			inner join project.products as p on (p.productid = q1.productid)
			inner join project.categories as c on (c.categoryid = p.categoryid)
	) as q2
   where
	q2.rank_num = 1
   order by 
	q2.categoryname

Продукт кој се јавил најмногу во листа на омилени продукти

   select
	q1.productid,
        p.productname,
        q1.num_users_favourite
   from
	 (
	        select
                   uf.productid as productid,
                   count(*) as num_users_favourite
	 	from
	 		project.userfavourites as uf
			group by
			      uf.productid
			order by
			      num_users_favourite desc
			fetch first rows with ties
	 
	 ) as q1
	inner join project.products as p on (p.productid = q1.productid)
	order by
	      q1.productid desc

Ранг на цени и просечна цена на продукти од секоја продавница

   select
	 s.storename,
	 
	 min(p.productinstanceprice) as min_price,
	 
	 max(p.productinstanceprice) as max_price,
	 
	 round(avg(p.productinstanceprice)) as avg_price
   from
	project.stores s
	inner join project.productinstances p on (p.storeid = s.storeid)
	
   group by
        s.storename
   order by
        avg(p.productinstanceprice)

Ранг на цени и просечна цена на продукти од секој производител

   select 
	m.manufacturername,
	
	p.productname,
	
	min(p2.productinstanceprice) as min_price,
	
	max(p2.productinstanceprice) as max_price,
	
	round(avg(p2.productinstanceprice)) as avg_price
	
   from
	project.manufacturers m 
	inner join project.products p on (p.manufacturerid = m.manufacturerid)
	inner join project.productinstances p2 on (p2.productid = p.productid)

   group by
         m.manufacturername,
         p.productname
   order by
         m.manufacturername,
         p.productname

Број на продукти од секоја категорија

   select
	c.categoryname,
	count(*) as num_products
   from
	project.categories c
	inner join project.products p on (p.categoryid = c.categoryid)
   group by
         c.categoryid
   order by
         num_products desc