SQL Bars?

While browsing through some SQL puzzles, I came across one where it asked to create a bar chart inside SQL server.

Initial thought - It will require some sort of text manipulation.
But before that I need to structure the data in proper format.

Input Table:-

The problem is divided into 3 parts:-
  1. Get regions and their total quantities.
  2. Because total quantities would be huge, it will not be possible to plot them as bars.
    Therefore, convert those quantities into percentage of total value.
  3. Create bars for percentage of total value against each region.

SQL script
with cte as
(
select Region, sum(Order_Quantity) as Quantity,
sum(SUM(Order_Quantity)) over () as [Total Quantity],
round(
(
CAST(
sum(Order_Quantity) as float ) / cast  (sum(sum(Order_Quantity)) over () as float )
) * 100
,0)
 as [% Total]
from superstore
group by Region
)
select Region, [% Total],
REPLICATE('|',[% Total]) as [Bars]
from cte
Output Table:-

It was a fun little exercise to think outside the box and how you can manipulate a tool to create something it's not originally intended to do.

Just to add final thought, will you ever use this in your real data analysis? Probably not.
But is it fun to do? Absolutely!

Comments

Popular posts from this blog

Superstore Executive Dashboard

The power of your passport