The avatar for @jzavala-gonzalez
José M. Zavala González
Public
Edited
Oct 24
Fork of OECD Paper
1 star
7
Insert cell
Insert cell
db_client
from cbc_variable
Insert cell
Insert cell
Inputs.table(cbc_labels)
Insert cell
cbc_labels = {
let thedata = [...cbc_variable_interes, iir_variable_interes]

thedata = thedata.map(d => {
let dnew = {};
for (const k of ['cbc', 'variable_en', 'variable_es']) {
dnew[k] = d[k]
}
return dnew
})

return thedata
}
Insert cell
cbc_variable_interes = {
let thedata = await db_client.query(`
from cbc_variable
where ${cbc_variable_interes_sql_list}.list_contains(cbc)
`)

const variables_with_aggregate = ['ETR', 'TTR', 'QDMTT'];
thedata.forEach(d => {
d.needs_aggregate = variables_with_aggregate.includes(d.cbc),
d.variable_en = (cbc_variable_en[d.cbc] ?? d.variable)
d.variable_es = cbc_variable_es[d.cbc]
})
return d3.sort(thedata, d => cbc_variable_interes_list.indexOf(d.cbc))
}
Insert cell
iir_variable_interes = Object({
cbc: 'IIR', variable: 'Income inclusion rule',
needs_aggregate: true,
variable_en: 'Income Inclusion Rule (IIR)', variable_es: 'Regla de inclusión de ingresos',
})
Insert cell
cbc_variable_interes_list = ['TOT_REV', 'PROFIT', 'TAX_ACCRUED', 'EMPLOYEES', 'ENTITIES_COUNT', 'ASSETS', 'ETR', 'EARNINGS', 'PAYROLL', 'TAX_BASE', 'SBIE', 'EXCESS_PROFIT', 'ETR', 'TTR', 'QDMTT']
Insert cell
cbc_variable_interes_no_aggregate_list = cbc_variable_interes.filter(d => !d.needs_aggregate).map(d => d.cbc)
Insert cell
cbc_variable_interes_list.map((d,i) => [d,i])
Insert cell
cbc_variable_needs_aggregate_sql = 'tax_accrued / profit as ETR, .15 - ETR as TTR, TTR * TAX_BASE as QDMTT'
Insert cell
Insert cell
panel_to_profit_label = Object({
'_T': 'Total', 'PANELAI': 'Positive', 'PANELAII': 'Negative'
})
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
[...pr_iir_projections, ...iir_projections]
Insert cell
Insert cell
Insert cell
Insert cell
totals_by_year_data
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell
downloadbuttonCSV(totals_by_year_data, `oecd_puerto_rico_totals_by_year_profit_${panel_to_profit_label[profit_grouping].toLowerCase()}.csv`)
Insert cell
`oecd_puerto_rico_totals_by_year_profit_${panel_to_profit_label[profit_grouping].toLowerCase()}.csv`
Insert cell
totals_by_year_data = await db_client.query(`\
with initial as (
select time_period,
-- sum(TOT_REV) as TOT_REV,
${cbc_variable_interes_no_aggregate_list.map(d => `sum(${d}) as ${d}`).join(', ')}
from oecd_puerto_rico
group by time_period
order by time_period
)

from initial select *, ${cbc_variable_needs_aggregate_sql}
`)
Insert cell
Insert cell
summary_variables_yearly = await db_client.query(`\
with initial as (
select time_period,
-- sum(TOT_REV) as TOT_REV,
${cbc_variable_interes_no_aggregate_list.map(d => `sum(${d}) as ${d}`).join(', ')}
from oecd_puerto_rico
group by time_period
order by time_period
),

totals_by_year as (
from initial select *, ${cbc_variable_needs_aggregate_sql}
),

first_unpivot as (

unpivot totals_by_year
on columns(* exclude (time_period))
into
name cbc
value value
)

from first_unpivot
`)
Insert cell
cbc_plot_options = Object({
'TOT_REV': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 130, tickPadding: 70,
bar_threshold: 1.8e9, outtext_spacing: 3.5e9,
},
'PROFIT': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 80,
bar_threshold: 700e6, outtext_spacing: 1e9,
},
'TAX_ACCRUED': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 100, tickPadding: 55,
bar_threshold: 15e6, outtext_spacing: 30e6,
},
'EMPLOYEES': {
tickFormat: d => d3.format(',.0f')(d),
marginLeft: 95, tickPadding: 55,
bar_threshold: 1200,
},
'ENTITIES_COUNT': {
marginLeft: 70, tickPadding: 45,
bar_threshold: 17,
},
'ETR': {
tickFormat: d => d3.format('.2%')(d),
bar_threshold: 0.1, outtext_spacing: 0.12,
},
'ASSETS': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
bar_threshold: 5e6, marginLeft: 90,
},
'EARNINGS': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 140, tickPadding: 70,
bar_threshold: 5000e6, outtext_spacing: 3000e6,
},
'PAYROLL': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 140, tickPadding: 70,
bar_threshold: 5e6,
},
'TAX_BASE': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 140, tickPadding: 70,
bar_threshold: 750e6, outtext_spacing: 1000e6,
},
'SBIE': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 140, tickPadding: 70,
bar_threshold: 100e6, outtext_spacing: 1000e6,
},
'EXCESS_PROFIT': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 140, tickPadding: 70,
bar_threshold: 3000e6, outtext_spacing: 3000e6,
},
'TTR': {
tickFormat: d => d3.format('.2%')(d),
bar_threshold: 0.1, outtext_spacing: 0.12,
},
'QDMTT': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 140, tickPadding: 70,
bar_threshold: 150e6, outtext_spacing: 170e6,
},
'IIR': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 140, tickPadding: 70,
bar_threshold: 100e6, outtext_spacing: 150e6,
},
})
Insert cell
cbc_plot_options_profit_positive = Object({
'TOT_REV': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 150, tickPadding: 70,
bar_threshold: 2.5e9, outtext_spacing: 3.0e9,
},
'PROFIT': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 80,
bar_threshold: 700e6, outtext_spacing: 1e9,
},
'TAX_ACCRUED': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 100, tickPadding: 55,
bar_threshold: 25e6, outtext_spacing: 30e6,
},
'EMPLOYEES': {
tickFormat: d => d3.format(',.0f')(d),
marginLeft: 95, tickPadding: 55,
bar_threshold: 1200,
},
'ENTITIES_COUNT': {
marginLeft: 70, tickPadding: 45,
bar_threshold: 10, outtext_spacing: 5,
},
'ETR': {
tickFormat: d => d3.format('.2%')(d),
bar_threshold: 0.02, outtext_spacing: 0.015,
},
'ASSETS': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
bar_threshold: 700e6, outtext_spacing: 700e6,
marginLeft: 90,
},
'EARNINGS': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 140, tickPadding: 70,
bar_threshold: 5000e6, outtext_spacing: 3000e6,
},
'PAYROLL': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 140, tickPadding: 70,
bar_threshold: 90e6, outtext_spacing: 90e6,
},
'TAX_BASE': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 140, tickPadding: 70,
bar_threshold: 750e6, outtext_spacing: 1000e6,
},
'SBIE': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 140, tickPadding: 70,
bar_threshold: 70e6, outtext_spacing: 70e6,
},
'EXCESS_PROFIT': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 140, tickPadding: 70,
bar_threshold: 3000e6, outtext_spacing: 3000e6,
},
'TTR': {
tickFormat: d => d3.format('.2%')(d),
bar_threshold: 0.02, outtext_spacing: 0.02,
},
'QDMTT': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 140, tickPadding: 70,
bar_threshold: 150e6, outtext_spacing: 170e6,
},
'IIR': {
tickFormat: d => d3.format('$,.1f')(d/1e6)+'M',
marginLeft: 140, tickPadding: 70,
bar_threshold: 100e6, outtext_spacing: 150e6,
},
})
Insert cell
Insert cell
Insert cell
Insert cell
d3.sort(m.get(2016), d => -d['TOT_REV'])
Insert cell
Insert cell
region_labels = {
let themap = Object({
'EU': ['Europe', 'Europa'],
'US': ['United States', 'Estados Unidos'],
'OTHER': ['Other countries', 'Otros países'],
})

let thedata = Object.entries(themap).map(d => Object({region: d[0], region_en: d[1][0], region_es: d[1][1]}))
return thedata
}
Insert cell
db_client
select distinct cou, ultimate_parent_jurisdiction, region
from oecd_puerto_rico
left join cou_regions
using (cou)
order by cou
Insert cell
totals_by_region_data
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell
totals_by_region_data = {
await db_client.query(`
create or replace table totals_by_region_data as (
with initial as (
select region, time_period,
${cbc_variable_interes_no_aggregate_list.map(d => `sum(${d}) as ${d}`).join(', ')}
from oecd_puerto_rico
left join cou_regions
using (cou)
group by all
order by all
)

from initial select *, ${cbc_variable_needs_aggregate_sql},
sum(tax_accrued) over (partition by time_period) / sum(profit) over (partition by time_period) as ETR_total,
(.15 - ETR_total) * TAX_BASE as IIR,
order by region, time_period
)
`)

let thedata = await db_client.query(`from totals_by_region_data`)
return thedata
}
Insert cell
Inputs.table(totals_by_region_data)
Insert cell
iir_projections
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell
iir_projections = {
// Dependencies:
totals_by_region_data;
iir_projections_scale_factors;
let thedata = await db_client.query(`
with iir_2021 as (
select region, time_period, iir,
from totals_by_region_data
where time_period = 2021
),

data_with_scale_factors as (
select iir_2021.*, sf.time_period as new_time_period, sf.scale_factor
from iir_2021
left join iir_projections_scale_factors as sf
on iir_2021.region = sf.region
)

select region, new_time_period as time_period, IIR, IIR * scale_factor as IIR_scaled,
from data_with_scale_factors
`)

return thedata
}
Insert cell
iir_projections_scale_factors = {
// For scaling down projections segun el
// numero de paises por region que aplican IIR

const region_data = [
{region: 'US', 2024: 0, 2025: .92746},
{region: 'EU', 2024: .8589, 2025: 7/7},
{region: 'OTHER', 2024: .9934, 2025: .9934}, // solo Japon tiene IIR
// {region: 'US', 2024: 1, 2025: 1},
// {region: 'EU', 2024: 1, 2025: 1},
// {region: 'OTHER', 2024: 1, 2025: 1},
]

let thedata = [];
for (let o of region_data) {
const region = o['region'];
thedata.push(...[
{region, time_period: 2024, scale_factor: o[2024]},
{region, time_period: 2025, scale_factor: o[2025]},
])
}

await db_client.query(`
create or replace table iir_projections_scale_factors (region VARCHAR, time_period INT, scale_factor FLOAT)
`)
await db_client.query(`insert into iir_projections_scale_factors values ` +
(thedata.map(d => `('${d.region}', ${d.time_period}, ${d.scale_factor})`)).join(', ')
)
return thedata
}
Insert cell
await db_client.query('from iir_projections_scale_factors')
Insert cell
cou_regions = {
const themap = Object({
'ARE': 'OTHER',
'SAU': 'OTHER',
'BMU': 'OTHER',
'CHE': 'EU',
'CYM': 'OTHER',
'DEU': 'EU',
'DNK': 'EU',
'ESP': 'EU',
'GRC': 'EU',
'HKG': 'OTHER',
'IND': 'OTHER',
'ITA': 'EU',
'JPN': 'OTHER',
'LUX': 'EU',
'MEX': 'OTHER',
'PAN': 'OTHER',
'PER': 'OTHER',
'USA': 'US',
})

const thedata = Object.entries(themap).map(d => Object({cou: d[0], region: d[1]}));

return thedata
}
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
{
const cou_in_profit_making = new Set(["CHE", "CYM", "DEU", "ESP", "GRC", "ITA", "JPN", "LUX", "SAU", "USA"]);

const cou_in_total = new Set(["ARE", "BMU", "CHE", "CYM", "DEU", "ESP", "GRC", "HKG", "ITA", "JPN", "LUX", "MEX", "SAU", "USA"]);

return cou_in_profit_making
}
Insert cell
chroma(EA_COLORES_MAP['INDIGO']).brighten(2)
Insert cell
cou_to_name_map
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
totals_by_country_data.filter(d => d.time_period === 2021).map(d => d.jur)
Insert cell
totals_by_country_data
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell
// totals_by_country_data =
{
let thedata = await db_client.query(`
with initial as (
select cou, time_period,
${cbc_variable_interes_no_aggregate_list.map(d => `sum(${d}) as ${d}`).join(', ')}
from oecd_puerto_rico
group by all
order by all
)

select *, ${cbc_variable_needs_aggregate_sql},
sum(tax_accrued) over (partition by time_period) / sum(profit) over (partition by time_period) as ETR_total,
(.15 - ETR_total) * TAX_BASE as IIR,
from initial
left join cou_regions
using (cou)
order by cou, time_period
`)
return thedata
}
Insert cell
totals_by_country_data =
{
let thedata = await db_client.query(`
with initial as (
select jur, partner_jurisdiction, time_period,
${cbc_variable_interes_no_aggregate_list.map(d => `sum(${d}) as ${d}`).join(', ')}
from oecd_all_countries
group by all
order by all
)

select initial.*, cou_regions.region, ${cbc_variable_needs_aggregate_sql},
sum(tax_accrued) over (partition by time_period) / sum(profit) over (partition by time_period) as ETR_total,
(.15 - ETR_total) * TAX_BASE as IIR,
from initial
left join cou_regions
on initial.jur = cou_regions.cou
order by cou, time_period
`)
return thedata
}
Insert cell
// cou_to_name =
{
let thedata = await db_client.query(`
select distinct cou, ultimate_parent_jurisdiction as label_en, pais as label_es
from oecd_puerto_rico
left join countries
on oecd_puerto_rico.cou = countries."alpha-3"
group by all
order by all
`)

// const cou_labels_es = Object({
// 'ARE': 'Reino de Arabia Saudita',
// })
// thedata.forEach(d => {
// const cou = d.cou;
// // if (cou_labels_es[d.cou] !== undefined) {
// d.label_es = cou_labels_es[d.cou] ?? undefined;
// // }
// })
return thedata
}
Insert cell
cou_to_name =
{
let thedata = await db_client.query(`
select distinct jur, partner_jurisdiction as label_en, pais as label_es
from oecd_all_countries
left join countries
on oecd_all_countries.jur = countries."alpha-3"
group by all
order by all
`)

// const cou_labels_es = Object({
// 'ARE': 'Reino de Arabia Saudita',
// })
// thedata.forEach(d => {
// const cou = d.cou;
// // if (cou_labels_es[d.cou] !== undefined) {
// d.label_es = cou_labels_es[d.cou] ?? undefined;
// // }
// })
return thedata
}
Insert cell
cou_to_name_map = {
let data = Object()
cou_to_name.forEach(d => {
data[d.jur] = d
})
return data
}
Insert cell
db_client
select sum(entities_count) as entities_count,
-- (ACT_ADMIN+ACT_DOR+ACT_FIN+ACT_HOLDING+ACT_IGF+ACT_INS+ACT_IP+ACT_MAN+ACT_OTHER+ACT_PUR+ACT_RD+ACT_SALES+ACT_SER) as total_in_act,
sum(list_value(ACT_ADMIN,ACT_DOR,ACT_FIN,ACT_HOLDING,ACT_IGF,ACT_INS,ACT_IP,ACT_MAN,ACT_OTHER,ACT_PUR,ACT_RD,ACT_SALES,ACT_SER).list_aggregate('sum')) as total_in_act,
sum(columns('ACT_*'))
from oecd_puerto_rico
where time_period = 2021
Insert cell
Insert cell
Plot.plot({
projection: "equirectangular",
r: {transform: (r) => Math.pow(10, r)}, // Richter to amplitude
marks: [
Plot.geo(land, {fill: "currentColor", fillOpacity: 0.2}),
Plot.sphere(),
// Plot.geo(earthquakes, {
// r: "mag",
// fill: "red",
// fillOpacity: 0.2,
// stroke: "red",
// title: "title",
// href: "url",
// target: "_blank"
// })
]
})
Insert cell
`
select time_period,
sum(TOT_REV) as TOT_REV,
${cbc_variable_interes_list.map(d => `sum(${d}) as ${d},`)}
from oecd_puerto_rico
group by time_period
`
Insert cell
db_client.query(`
select time_period,
sum(TOT_REV) as TOT_REV,
${cbc_variable_interes_no_aggregate_list.map(d => `sum(${d}) as ${d}`).join(', ')}
from oecd_puerto_rico
group by time_period
`)
Insert cell
Insert cell
db_client
from oecd_puerto_rico
Insert cell
db_client
from cbc_variable
Insert cell
db_client
from countries
Insert cell
cbc_variable_en = Object({
'ETR': 'Effective Tax Rate (ETR)',
'QDMTT': 'Qualifying Domestic Minimum Top-up Tax (QDMTT)',
'SBIE': 'Substance-Based Income Exclusion (SBIE)',
'TTR': 'Top-up Tax Rate (TTR)',
'IIR': 'Income Inclusion Rule (IIR)'
})
Insert cell
cbc_variable_es = Object({
'TOT_REV': 'Ingresos totales',
'PROFIT': 'Beneficios',
'TAX_ACCRUED': 'Impuestos devengados',
'EMPLOYEES': 'Empleos',
'ENTITIES_COUNT': 'Subsidiarias',
'ETR': 'Tasa efectiva contributiva',
'ASSETS': 'Activos tangibles',
'EARNINGS': 'Ganancias acumuladas',
'PAYROLL': 'Nómina',
'TAX_BASE': 'Base tributaria',
'SBIE': 'Exclusión de ingresos basada en la sustancia',
'EXCESS_PROFIT': 'Beneficio excedente',
'TTR': 'Impuesto complementario',
'QDMTT': 'Impuesto complementario mínimo nacional calificado (QDMTT)',
})
Insert cell
Insert cell
Insert cell
Insert cell
duckdb = import(
"https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.1-dev232.0/+esm"
)
Insert cell
db_client
from cou_regions
Insert cell
db_instance = {
// Pick a bundle
const bundles = duckdb.getJsDelivrBundles();
const bundle = await duckdb.selectBundle(bundles);

// Initialize the database
const logger = new duckdb.ConsoleLogger();
const worker = await duckdb.createWorker(bundle.mainWorker);
const db = new duckdb.AsyncDuckDB(logger, worker);
await db.instantiate(bundle.mainModule);
const connection = await db.connect()

// Import dataset files
// await insertFile(db, 'oecd_puerto_rico', oecd_puerto_rico_file)
// await insertFile(db, 'cbc_variable', cbc_variable_file)
await insertFile(db, 'oecd_all_countries_file', oecd_all_countries_file)

// Insert object arrays manually
await db.registerFileBuffer('oecd_puerto_rico.csv', encoder.encode( d3.csvFormat(await oecd_puerto_rico_file.csv({typed: true})) ))
await connection.insertCSVFromPath('oecd_puerto_rico.csv', {name: 'oecd_puerto_rico_file'})

await db.registerFileBuffer('cbc_variable.csv', encoder.encode( d3.csvFormat(await cbc_variable_file.csv({typed: true})) ))
await connection.insertCSVFromPath('cbc_variable.csv', {name: 'cbc_variable_file'})

await db.registerFileBuffer('ISO.csv', encoder.encode(
d3.csvFormat(cou_iso) ))
await connection.insertCSVFromPath('ISO.csv', {name: 'countries'})
await db.registerFileBuffer('cou_regions.csv', encoder.encode(d3.csvFormat(cou_regions)))
await connection.insertCSVFromPath('cou_regions.csv', {name: 'cou_regions'})

await db.registerFileBuffer('monthly_earnings_simplified.csv', encoder.encode(d3.csvFormat(await monthly_earnings_simplified_file.csv({typed: true})) ))
await connection.insertCSVFromPath('monthly_earnings_simplified.csv', {name: 'monthly_earnings_simplified_file'})
// const buffer = await oecd_cbcr_file.arrayBuffer();
// await db_instance.registerFileBuffer(oecd_cbcr_file.name, new Uint8Array(buffer));
// await client.query(`create or replace view 'oecd_cbcr' as select * from parquet_scan(${oecd_cbcr_file.name})`)
return db
}
Insert cell
cou_iso = {
const cou_iso = await iso_file.csv({typed: true});
cou_iso.forEach(d => {
d['pais'] = cou_alpha2_map[d['alpha-2']]
})
return cou_iso

}
Insert cell
cou_alpha2_map = {
const paises = await paises_file.json()
let thedata = {}
paises.forEach(d => {
thedata[d.code] = d.country
})
return thedata
}
Insert cell
import {world, land} from "@observablehq/plot-live-earthquake-map"
Insert cell
import {DuckDBClientCompat} from "@jzavala-gonzalez/duckdb-tpc-h"
Insert cell
weighted_avg_salary = {
let salary = 41840.46 // el valor original calculado
// salary = salary * 1.20 // prima de 20% // Leave for later en el SQL
salary = Math.round(salary * 100) / 100 // round a dos decimales

return salary
}
Insert cell
db_client
select oecd.*, round(me.earnings * 12 * 1.20, 2) as average_salary
from oecd_all_countries_file as oecd
left join monthly_earnings_simplified_file as me
on oecd.jur = me.ref_area
Insert cell
db_client
from oecd_all_countries
Insert cell
db_client
from monthly_earnings_simplified
Insert cell
db_client = {
const client = await new DuckDBClientCompat(db_instance)

await client.query(`create or replace table monthly_earnings_simplified as (
select * from monthly_earnings_simplified_file
)`)
await client.query(`insert into monthly_earnings_simplified values
('PRI', 'Puerto Rico', 2021, ${weighted_avg_salary / 12})
`)

await client.query(`create or replace table oecd_all_countries as (
with oecd_all_countries_file_with_salary as (
select oecd.*, round(me.earnings * 12 * 1.20, 2) as average_salary
from oecd_all_countries_file as oecd
left join monthly_earnings_simplified as me
on oecd.jur = me.ref_area
)

select *,
EMPLOYEES * average_salary as PAYROLL,
PROFIT - .08*ASSETS - .10*PAYROLL as TAX_BASE,
.10*PAYROLL + .08*ASSETS as SBIE,
TOT_REV - SBIE as EXCESS_PROFIT,
from (from oecd_all_countries_file_with_salary where profit_grouping = '${profit_grouping}')
)`)

await client.query(`create or replace table oecd_puerto_rico as (
select *,
EMPLOYEES * ${weighted_avg_salary} as PAYROLL,
PROFIT - .08*ASSETS - .10*PAYROLL as TAX_BASE,
.10*PAYROLL + .08*ASSETS as SBIE,
TOT_REV - SBIE as EXCESS_PROFIT,
from (from oecd_puerto_rico_file where profit_grouping = '${profit_grouping}')
)`)

// await client.query(`create or replace table cbc_variable_backup as (from cbc_variable)`);
await client.query(`create or replace table cbc_variable (cbc varchar primary key, variable varchar)`);
await client.query(`insert into cbc_variable select * from cbc_variable_file`)

await client.query(`insert into cbc_variable values
('PAYROLL', 'Payroll'),
('TTR', 'Top-up tax rate'),
('TAX_BASE', 'Tax base'),
('SBIE', 'Substance based income exclusion'),
('EXCESS_PROFIT', 'Excess profit'),
('QDMTT', 'Qualifying domestic minimum top-up tax'),
('IIR', 'Income inclusion rule')
`)


return client
}
Insert cell
db_client
from cbc_variable
Insert cell
async function insertFile(database, name, file, options) {
const url = await file.url();
if (url.startsWith("blob:")) {
const buffer = await file.arrayBuffer();
await database.registerFileBuffer(file.name, new Uint8Array(buffer));
} else {
await database.registerFileURL(file.name, new URL(url, location).href, 4); // duckdb.DuckDBDataProtocol.HTTP
}
const connection = await database.connect();
try {
switch (file.mimeType) {
case "text/csv":
case "text/tab-separated-values": {
return await connection.insertCSVFromPath(file.name, {
name,
schema: "main",
...options
}).catch(async (error) => {
// If initial attempt to insert CSV resulted in a conversion
// error, try again, this time treating all columns as strings.
if (error.toString().includes("Could not convert")) {
return await insertUntypedCSV(connection, file, name);
}
throw error;
});
}
case "application/json":
return await connection.insertJSONFromPath(file.name, {
name,
schema: "main",
...options
});
default:
if (/\.arrow$/i.test(file.name)) {
const buffer = new Uint8Array(await file.arrayBuffer());
return await connection.insertArrowFromIPCStream(buffer, {
name,
schema: "main",
...options
});
}
if (/\.parquet$/i.test(file.name)) {
return await connection.query(
`CREATE VIEW '${name}' AS SELECT * FROM parquet_scan('${file.name}')`
);
}
throw new Error(`unknown file type: ${file.mimeType}`);
}
} finally {
await connection.close();
}
}
Insert cell
encoder.encode('abcd').buffer
Insert cell
Insert cell
Insert cell
cbc_variable_file = FileAttachment("cbc_variable@1.csv")
Insert cell
oecd_puerto_rico_file = FileAttachment("oecd_puerto_rico@4.csv")
Insert cell
oecd_all_countries_file = FileAttachment("oecd_all_countries@1.parquet")
Insert cell
monthly_earnings_simplified_file = FileAttachment("monthly_earnings_simplified.csv")
Insert cell
iso_file = FileAttachment("ISO.csv")
Insert cell
paises_file = FileAttachment("paises.json")
Insert cell
encoder = new TextEncoder();
Insert cell
oecd_puerto_rico_file.csv({typed: true})
Insert cell
Insert cell
Insert cell
Insert cell
estilos = htl.html`
<style>
h1 {
font-family: "jaf-bernino-sans-comp", sans-serif;
font-weight: 700;
font-style: normal;
}

h2 {
font-family: "jaf-bernino-sans-narrow", sans-serif;
font-weight: 700;
font-style: normal;
}

span {
font-family: "jaf-bernino-sans", sans-serif;
font-weight: 400;
font-style: normal;
}

span[class$="-swatch"] {
font-size: 16px;
}
</style>
`
Insert cell
import { EA_COLORES_MAP } from "22dc6182207c44c8"
Insert cell
import {downloadbuttonCSV} from "@pavlos/download-buttons"
Insert cell
chroma = require("chroma-js@2.4.2")
Insert cell

One platform to build and deploy the best data apps

Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.
Learn more