USE OLAP
--Raw Data
SELECT p.product,count(*) number_of_products_sold
FROM products as p JOIN sales as s ON p.code=s.product_code
GROUP BY p.product
ORDER BY number_of_products_sold desc

--Replicate
SELECT replicate('*',20) as twenty_stars

--Bar Chart
SELECT p.product,replicate('*',count(*)) number_of_products_sold
FROM products as p JOIN sales as s ON p.code=s.product_code
GROUP BY p.product
ORDER BY number_of_products_sold desc

--Scale
WITH bar_graph as
	(SELECT p.product,count(*) number_of_products_sold
	FROM products as p JOIN sales as s ON p.code=s.product_code
	GROUP BY p.product
	--ORDER BY number_of_products_sold desc
	)
SELECT min(number_of_products_sold) as range_from,max(number_of_products_sold) as range_to
FROM bar_graph

--So we are best using a star for every 10 proudcts sold
SELECT p.product,replicate('*',count(*)/10) number_of_products_sold
FROM products as p JOIN sales as s ON p.code=s.product_code
GROUP BY p.product
ORDER BY number_of_products_sold desc

--Improve the layout
SELECT p.product,replicate(nchar(9608),count(*)/10) as number_of_products_sold
FROM products as p JOIN sales as s ON p.code=s.product_code
GROUP BY p.product
ORDER BY number_of_products_sold desc

--Using variables
declare @char nchar = nchar(9608) --you can change the character you use at any time
declare @scale int = 10 --you can change your scale too
SELECT p.product,replicate(@char,count(*)/@scale) as number_of_products_sold
FROM products as p JOIN sales as s ON p.code=s.product_code
GROUP BY p.product
ORDER BY number_of_products_sold desc

--Different characters and scale:
declare @char nchar = '-' --you can change the character you use at any time
declare @scale int = 5 --you can change your scale too
SELECT p.product,replicate(@char,count(*)/@scale) as number_of_products_sold
FROM products as p JOIN sales as s ON p.code=s.product_code
GROUP BY p.product
ORDER BY number_of_products_sold desc

--Non-integer values
SELECT product
       ,replicate(nchar(9608),round(product_cost,0)) as cost
FROM products
ORDER BY product_cost asc

--Padding out headers
SELECT product
	   --add spaces to header
       ,replicate(nchar(9608),round(product_cost,0)) as [cost                                 ]
FROM products
ORDER BY product_cost asc

--Finally it might also be useful to include the price itself
SELECT product
	   --add spaces to header
       ,replicate(nchar(9608),round(product_cost,0)) as [cost                                 ]
	   ,FORMAT(product_cost,'C') as value
FROM products
ORDER BY product_cost asc
