--#connection "sqlite:~#\..\Data\WorldData.db"
--#table Regions
--#format table Regions for "Contains([Short Name], 'Euro')" with BackColor=Yellow
--#format table Regions column [Table Name] with Condition=Equal, Value1='OECD members', BackColor=Green, ApplyToRow=true
select ID, [Country Code], [Short Name], [Table Name], [Long Name],
[2-alpha code], [Currency Unit], [Special Notes], [WB-2 code],
[National accounts base year],
[Balance of Payments Manual in use], [External debt Reporting status],
[Latest agricultural census], [Latest industrial data], [Latest trade data]
from Countries
where ifnull(Region, '') = '';
--#table Countries with GroupBy="Region"
--#format table Countries for "Contains([Region], 'Europe')" with BackColor=Yellow
select ID, [Country Code], [Short Name], [Table Name], [Long Name],
[2-alpha code], [Currency Unit], [Special Notes], [Region],
[Income group], [WB-2 code], [National accounts base year],
[National accounts reference year], [SNA price valuation],
[Lending category], [Other groups], [System of National Accounts],
[Alternative conversion factor], [PPP survey year],
[Balance of Payments Manual in use], [External debt Reporting status],
[System of trade], [Government Accounting concept],
[IMF data dissemination standard], [Latest population census],
[Latest household survey], [Source of most recent Income and expenditure data],
[Vital registration complete], [Latest agricultural census],
[Latest industrial data], [Latest trade data]
from Countries
where Region > '';
/*#
table Series with GroupBy="Topic Group", OrderBy="Topic Part desc";
format table Series column [Indicator Name] for "Contains([Indicator Name], 'Agricultural')" with BackColor=Green, ForeColor=White;
format table Series column Topic for "Contains([Topic], 'Economic')" with BackColor=LightGreen;
computed column [Topic Part] string in Series = "REGEXMATCH([Topic], '(?<=:).*')";
#*/
select ID, [Series Code], RegexMatch([Series Code], '[^\.]+') as [Series Group],
RegexMatch([Series Code], '[^\.]+', 1) as [Series SubGroup 1],
RegexMatch([Series Code], '[^\.]+', 2) as [Series SubGroup 2],
RegexMatch([Series Code], '[^\.]+', 3) as [Series SubGroup 3],
RegexMatch([Series Code], '[^\.]+', 4) as [Series SubGroup 4],
[Topic], RegexMatch([Topic], '[^:]*') as [Topic Group],
[Indicator Name], [Short definition],
[Long definition], [Unit of measure], [Periodicity], [Base period],
[Other notes], [Aggregation method], [Limitations and exceptions],
[Notes from original source], [General comments], [Source],
[Statistical concept and methodology], [Development relevance],
[Related source links], [Other web links], [Related indicators],
[License Type]
from Series;
--#table "Energy use - by Regions"
--#format table "Energy use - by Regions" column MaxValue with IconSet=Signs3
--#format table "Energy use - by Regions" column MinValue with IconSet=Arrows3
--#relation Rel_Regions_EnergyUse [Regions] ([Short Name]) - [Energy use - by Regions] ([Region])
select c.Region,
min(pp.Value) as MinValue,
max(pp.Value) as MaxValue,
avg(pp.Value) as AverageValue,
median(pp.Value) as MedianValue,
stdev(pp.Value) as StdDev
from [EG.GDP.PUSE.KO.PP] pp
join Countries c on c.[Country Code] = pp.[Country Code]
where Year = 2014 and Value is not null and c.Region > ''
group by c.Region
order by Region;
--#table "Energy use - by Income"
--#format table [Energy use - by Income] column [AverageValue] with Rule=DataBar, BackColor='LightGreen', BackColor2='LightGray', Gradient=Horizontal
--#relation Rel_EnergyUse_Countries [Energy use - by Income] ([Income Group]) - [Countries] ([Income Group])
select c.[Income Group],
min(pp.Value) as MinValue,
max(pp.Value) as MaxValue,
avg(pp.Value) as AverageValue,
median(pp.Value) as MedianValue,
stdev(pp.Value) as StdDev
from [EG.GDP.PUSE.KO.PP] pp
join Countries c on c.[Country Code] = pp.[Country Code]
where Year = 2014 and Value is not null and c.Region > ''
group by c.[Income Group]
order by case [Income Group]
when 'Low income' then 1
when 'Lower middle income' then 2
when 'Upper middle income' then 3
when 'High income' then 4 end;
--#table "Energy use"
--#format table "Energy use" column [Income Rank] with IconSet=Rating5
--#format table "Energy use" column Value with IconSet=Arrows5
--#format table "Energy use" column Value with Rule=AboveAverage, BackColor=LightGreen
--#format table "Energy use" column Value with Rule=BelowAverage, BackColor=Red, ForeColor=White
select pp.ID, pp.[Country Code], c.[Table Name] as Country, c.Region,
c.[Income Group], case [Income Group]
when 'Low income' then 1
when 'Lower middle income' then 2
when 'Upper middle income' then 3
when 'High income' then 4 end as [Income Rank],
pp.Year, pp.Value
from [EG.GDP.PUSE.KO.PP] pp
join Countries c on c.[Country Code] = pp.[Country Code]
where Year = 2014 and Value is not null and c.Region > ''
order by Country;