The Global Human Day

How do people allocate their time in a day? Explore differences by country. Project based on resesarch provided by The Global Human Day study.

Summary

This project is based on data provided by a research study called The Global Human Day. The study aims to create a “global estimate of time use by all humans” and provides a snapshot of how humans allocate time across various activities.

There are a total of 20 activity classifications, or subcategories, that align with 8 parent categories (Somatic maintenance, Experience oriented, Food provision, Nonfood provision, Organization, Deliberate neural restructuring, Maintenance of surroundings, and Technosphere modification).

Information is available per country. This project explores how people in different countries prioritize their time across different activities.

d3 = require("d3@5", "d3-weighted-voronoi", "d3-voronoi-map", "d3-voronoi-treemap", 'seedrandom@2.4.3/seedrandom.min.js')

Plot = import("https://esm.sh/@observablehq/plot")

mutable clickedSubcategory = "Food preparation"
mutable hoveredCategory = null

countries = [...new Set(tt_ghd_countries.map(f => getCountryNameByISO3(f.country_iso3)))].sort()
subcategories = [...new Set(tt_ghd_countries.map(f => f.Subcategory))]
// echo: false
tt_ghd_countries = d3.csv("https://raw.githubusercontent.com/rfordatascience/tidytuesday/master/data/2023/2023-09-12/all_countries.csv", d3.autoType)
tt_countries = d3.csv("https://raw.githubusercontent.com/rfordatascience/tidytuesday/master/data/2023/2023-09-12/country_regions.csv", d3.autoType)

selectedIsoCode = tt_countries.filter(d => {return d.country_name === selectedCountry})[0].country_iso3

tt_country = tt_ghd_countries.filter(d => {return d.country_iso3 === selectedIsoCode})

d3 = Object {format: ƒ(t), formatPrefix: ƒ(t, n), timeFormat: ƒ(t), timeParse: ƒ(t), utcFormat: ƒ(t), utcParse: ƒ(t), FormatSpecifier: ƒ(t), active: ƒ(t, n), arc: ƒ(), area: ƒ(), areaRadial: ƒ(), ascending: ƒ(t, n), autoType: ƒ(t), axisBottom: ƒ(t), axisLeft: ƒ(t), axisRight: ƒ(t), axisTop: ƒ(t), bisect: ƒ(n, e, r, i), bisectLeft: ƒ(n, e, r, i), bisectRight: ƒ(n, e, r, i), …}

Plot = Module {Area: class, Arrow: class, BarX: class, BarY: class, Cell: class, Contour: class, Density: class, Dot: class, Frame: class, Geo: class, Hexgrid: class, Image: class, Line: class, Link: class, Mark: class, Raster: class, Rect: class, RuleX: class, RuleY: class, Text: class, …}

mutable clickedSubcategory = Mutable {}

clickedSubcategory = "Food preparation"

mutable hoveredCategory = Mutable {}

hoveredCategory = null

countries = Array(199) ["Afghanistan", "Albania", "Algeria", "Angola", "Antigua and Barbuda", "Argentina", "Armenia", "Aruba", "Australia", "Austria", "Azerbaijan", "Bahamas", "Bahrain", "Bangladesh", "Barbados", "Belarus", "Belgium", "Belize", "Benin", "Bhutan", …]

subcategories = Array(24) ["Food preparation", "Food growth & collection", "Food processing", "Materials", "Energy", "Infrastructure", "Buildings", "Artifacts", "Inhabited environment", "Sleep & bedrest", "Waste management", "Physical child care", "Health care", "Hygiene & grooming", "Schooling & research", "Religious practice", "Human transportation", "Material transportation", "Allocation", "Social", …]

tt_ghd_countries = Array(4776) [Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, …]

tt_countries = Array(248) [Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, …]

selectedIsoCode = "AFG"

tt_country = Array(24) [Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, …]

height = 700 - margin.top - margin.bottom
width = 700 - margin.left - margin.right
margin = ({top: 50, right: 80, bottom: 50, left: 20})

ellipse = d3
  .range(100)
  .map(i => [
    (width * (1 + 0.99 * Math.cos((i / 50) * Math.PI))) / 2,
    (height * (1 + 0.99 * Math.sin((i / 50) * Math.PI))) / 2
  ])

height = 600

width = 600

margin = Object {top: 50, right: 80, bottom: 50, left: 20}

ellipse = Array(100) [Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), Array(2), …]

categoryColor = function(category) {
  var colors = {
    "Food provision": "#48EB70",
    "Nonfood provision": "#FF9D13",
    "Technosphere modification": "#DCDCDC",
    "Maintenance of surroundings": "#F95738",
    "Somatic maintenance": "#EBCD49",
    "Deliberate neural restructuring": "#4DEAFF",
    "Organization" : "#00BFB2", 
    "Experience oriented" : "#7F2FFF"
  };
  return colors[category];
}

function colorHierarchy(hierarchy) {
  if(hierarchy.depth === 0) {
    hierarchy.color = 'black';
  } else if(hierarchy.depth === 1){
    hierarchy.color = categoryColor(hierarchy.data.key);
  } else {
    hierarchy.color = hierarchy.parent.color;
  }
  if(hierarchy.children) {
    hierarchy.children.forEach( child => colorHierarchy(child))
  } 
}

function decimalToHoursMinutes(decimal) {
  // Calculate the hours by rounding down the decimal number
  const hours = Math.floor(decimal);

  // Calculate the remaining minutes by multiplying the decimal part by 60
  const minutes = Math.round((decimal - hours) * 60);

  let timeString = '';

  // Add hours to the output only if it's greater than 0
  if (hours > 0) {
    timeString += `${hours} hr${hours > 1 ? 's' : ''}`;
  }

  // Add minutes to the output only if it's greater than 0
  if (minutes > 0) {
    if (timeString) {
      timeString += ' ';
    }
    timeString += `${minutes} min`;
  }

  // If neither hours nor minutes are greater than 0, return "0 min"
  if (!timeString) {
    timeString = "0 min";
  }

  return timeString;
}


function customTickFormat(value) {
  const hours = Math.floor(value);
  const minutes = Math.round((value - hours) * 60);
  return `${hours}:${String(minutes).padStart(2, '0')}`;
}

function getCountryNameByISO3(code) {
  // Assuming tt_countries is an array of country objects
  // with 'country_name' and 'country_iso3' fields
  const country = tt_countries.find((item) => item.country_iso3 === code);

  if (country) {
    return country.country_name;
  } else {
    return "Country not found"; // You can customize the error message
  }
}

categoryColor = ƒ(category)

colorHierarchy = ƒ(hierarchy)

decimalToHoursMinutes = ƒ(decimal)

customTickFormat = ƒ(value)

getCountryNameByISO3 = ƒ(code)

country_nested = {
  let country_nest = d3.nest()
      .key(d => d.Category)
      .entries(tt_country)
  
  return {key: "country_nest", values: country_nest}
}

category_hierarchy = d3.hierarchy(country_nested, d => d.values)
  .sum(d => d.hoursPerDayCombined)

country_nested = Object {key: "country_nest", values: Array(8)}

category_hierarchy = zl {data: Object, height: 2, depth: 0, parent: null, children: Array(8), value: 24.009999999999998}

htl.html`<h2>Breakdown by Category: ${selectedCountry}</h2>`

Breakdown by Category: Afghanistan

viewof selectedCountry = Inputs.select(countries, {label: "Country"})

Country AfghanistanAlbaniaAlgeriaAngolaAntigua and BarbudaArgentinaArmeniaArubaAustraliaAustriaAzerbaijanBahamasBahrainBangladeshBarbadosBelarusBelgiumBelizeBeninBhutanBolivia (Plurinational State of)Bosnia and HerzegovinaBotswanaBrazilBrunei DarussalamBulgariaBurkina FasoBurundiCabo VerdeCambodiaCameroonCanadaCentral African RepublicChadChileChinaChina, Hong Kong Special Administrative RegionChina, Macao Special Administrative RegionColombiaComorosCongoCosta RicaCroatiaCubaCuraçaoCyprusCzechiaCôte d’IvoireDemocratic People's Republic of KoreaDemocratic Republic of the CongoDenmarkDjiboutiDominican RepublicEcuadorEgyptEl SalvadorEquatorial GuineaEritreaEstoniaEswatiniEthiopiaFijiFinlandFranceFrench GuianaFrench PolynesiaGabonGambiaGeorgiaGermanyGhanaGreeceGrenadaGuadeloupeGuamGuatemalaGuineaGuinea-BissauGuyanaHaitiHondurasHungaryIcelandIndiaIndonesiaIran (Islamic Republic of)IraqIrelandIsraelItalyJamaicaJapanJordanKazakhstanKenyaKiribatiKuwaitKyrgyzstanLao People's Democratic RepublicLatviaLebanonLesothoLiberiaLibyaLithuaniaLuxembourgMadagascarMalawiMalaysiaMaldivesMaliMaltaMartiniqueMauritaniaMauritiusMayotteMexicoMicronesia (Federated States of)MongoliaMontenegroMoroccoMozambiqueMyanmarNamibiaNepalNetherlandsNew CaledoniaNew ZealandNicaraguaNigerNigeriaNorth MacedoniaNorwayOmanPakistanPanamaPapua New GuineaParaguayPeruPhilippinesPolandPortugalPuerto RicoQatarRepublic of KoreaRepublic of MoldovaRomaniaRussian FederationRwandaRéunionSaint LuciaSaint Vincent and the GrenadinesSamoaSao Tome and PrincipeSaudi ArabiaSenegalSerbiaSeychellesSierra LeoneSingaporeSlovakiaSloveniaSolomon IslandsSomaliaSouth AfricaSouth SudanSpainSri LankaState of PalestineSurinameSwedenSwitzerlandSyrian Arab RepublicTaiwanTajikistanThailandTimor-LesteTogoTongaTrinidad and TobagoTunisiaTurkeyTurkmenistanUgandaUkraineUnited Arab EmiratesUnited Kingdom of Great Britain and Northern IrelandUnited Republic of TanzaniaUnited States Virgin IslandsUnited States of AmericaUruguayUzbekistanVanuatuVenezuela (Bolivarian Republic of)Viet NamWestern SaharaYemenZambiaZimbabwe

selectedCountry = "Afghanistan"

voronoiMap(hoveredCategory)

{
const width = 550;
const height = 600;
const margin = { top: 20, right: 5, bottom: 30, left: 10 };

const svg = d3.create("svg")
.attr("viewBox", [0, 0, width, height])
.attr("style", "max-width: 100%; height: auto; height: intrinsic; background-color:transparent");


// Define your data (assuming you have a 'query' dataset)
const data = query

const categories = [...new Set(data.map(f => f.category))]

// Create scales for x and y axes
const xScale = d3.scaleLinear()
  .domain([0, 30])
  .range([margin.left, width - margin.right]);

const yScale = d3.scaleBand()
  .domain(categories)
  .range([margin.top, height - margin.bottom])
  .padding(0.1);

// Add circles and assign unique IDs based on 'category'
svg.selectAll("circle")
  .data(data)
  .enter()
  .append("circle")
  .attr("cx", d => xScale(2))
  .attr("cy", d => yScale(d.category))
  .attr("r", 15)
  .attr("fill", d => categoryColor(d.category))
  .attr("id", d => `${d.category}`) // Assign a unique ID based on 'category'
  .style("fill-opacity", 1) // Set initial fill-opacity to 0.5 for all circles
  .on("mouseover", function (d) {
    
    const hoveredId = `${d.category}`;

    mutable hoveredCategory = hoveredId;
    
    svg.selectAll("circle")
      .style("fill-opacity", circle => (circle.category === hoveredId ? 1 : 0.5));

    svg.selectAll(".title")
      .style("fill-opacity", circle => (circle.category === hoveredId ? 1 : 0.5))
      .style("font-weight", circle => (circle.category === hoveredId ? 'bold' : 'normal'));

    svg.selectAll(".value")
      .style("fill-opacity", circle => (circle.category === hoveredId ? 1 : 0.5))
      .style("font-weight", circle => (circle.category === hoveredId ? 'bold' : 'normal'));

  })
  
  .on("mouseout", function () {

    mutable hoveredCategory = null;
    // Reset fill-opacity for all circles
    svg.selectAll("circle").style("fill-opacity", 1);
    svg.selectAll(".title").style("fill-opacity", 1).style('font-weight','normal');
    svg.selectAll(".value").style("fill-opacity", 1).style('font-weight','normal');
  });





svg.selectAll(".title")
  .data(data)
  .enter()
  .append("text")
  .attr("class", "title")
  .attr("x", d => xScale(4))
  .attr("y", d => yScale(d.category))
  .attr("font-size", 20)
  .attr("font-family", "Oswald")
  .attr("dy", "0.35em")
  .text(d => d.category);
  
svg.selectAll(".value")
  .data(data)
  .enter()
  .append("text")
  .attr("class", "value")
  .attr("x", d => xScale(25))
  .attr("y", d => yScale(d.category))
  .attr("font-size", 18)
  .attr("font-family", "Roboto Condensed")
  .attr("text-anchor", "end")
  .attr("dy", "0.35em")
  .text(d => decimalToHoursMinutes(d.country_stat))
  .sort((a, b) => d3.ascending(a.text, b.text));

  return svg.node();
}

htl.html`<h2>How does ${selectedCountry} compare to other countries?</h2><p>Select a subcategory in the dropdown below or the treemap diagram above to see how other countries compare for a given activity.</p>`

How does Afghanistan compare to other countries?

Select a subcategory in the dropdown below or the treemap diagram above to see how other countries compare for a given activity.

viewof selectedSubcategory = Inputs.select(subcategories, {label: "Subcategory", value: clickedSubcategory})

htl.html`<div style='margin-top:15px;margin-bottom:30px;'>People in <strong style='color:#1B98E0;'>${selectedCountry}</strong> spend on average <strong>${decimalToHoursMinutes(textHours)}</strong> for <strong>${selectedSubcategory}</strong>. This is <strong>${compareHours(textHours, textMedian)} than</strong> the <span style='border-bottom: 2px dashed;'>global median</span> by <strong/>${decimalToHoursMinutes(Math.abs(textHours-textMedian))}</strong>.</div>`

Subcategory Food preparationFood growth & collectionFood processingMaterialsEnergyInfrastructureBuildingsArtifactsInhabited environmentSleep & bedrestWaste managementPhysical child careHealth careHygiene & groomingSchooling & researchReligious practiceHuman transportationMaterial transportationAllocationSocialActive recreationMealsPassiveInteractive

selectedSubcategory = "Food preparation"

People in Afghanistan spend on average 1 hr 8 min for Food preparation. This is higher than the global median by 20 min.

textHours = subset.filter(d => (d.Subcategory === selectedSubcategory & d.country_iso3 === selectedIsoCode))[0].hoursPerDayCombined
textMedian = d3.median(subset.filter(d => d.Subcategory === selectedSubcategory), d=> d.hoursPerDayCombined)

function compareHours(textHours, textMedian) {
  // Convert the input values to numbers (assuming they represent numbers)
  const hours = parseFloat(textHours);
  const median = parseFloat(textMedian);

  if (!isNaN(hours) && !isNaN(median)) {
    if (hours > median) {
      return "higher";
    } else {
      return "less";
    }
  } else {
    // Handle the case where input values are not valid numbers
    return "Invalid input";
  }
}

textHours = 1.14

textMedian = 0.81

compareHours = ƒ(textHours, textMedian)

subset = tt_ghd_countries.filter(d => d.Subcategory === selectedSubcategory)
// Create the Observable Plot with the custom tick format
Plot.plot({
  height: 600,
  width: 1200,
  marginBottom: 50,
  className: "horizontal-swarm",
  style: {background:"transparent", fontSize:15, fontFamily:"Roboto Condensed"},
  x: {
    tickSize: 0,
    label: 'HRS : MIN',
    tickFormat: customTickFormat
  },
  y: {ticks:false},
  marks: [
    Plot.dot(subset, Plot.dodgeY({
      r: 9,
      anchor: "middle",
      title : "country_iso3",
      padding: 2,
      fill: d => d.country_iso3 === selectedIsoCode ? '#1B98E0' : '#DFDFDF',
      x: "hoursPerDayCombined",
    })),
    Plot.dot(subset, Plot.dodgeY(
      Plot.pointer({
      r: 9,
      anchor: "middle",
      title : d=> getCountryNameByISO3(d.country_iso3) + '\n' + decimalToHoursMinutes(d.hoursPerDayCombined),
      padding: 2,
      fill: '#00C49A',
      tip: "xy",
      x: "hoursPerDayCombined"})
    )),
    Plot.ruleX(subset, Plot.groupZ({ x: "median" }, {
      x: "hoursPerDayCombined",
      stroke: "#393939",
      strokeWidth: 2.5,
      strokeDasharray: [5, 5],
    })),
  ],
});

subset = Array(199) [Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, …]

Plot.plot({
  height: 1200,
  width: 600,
  className: "vertical-swarm",
  style: {background:"transparent", fontSize:15, fontFamily:"Roboto Condensed"},
  y: {
    tickSize: 0,
    label: 'HRS : MIN',
    tickFormat: customTickFormat
  },
  x: {ticks:false},
  marks: [
    Plot.dot(subset, Plot.dodgeX({
      r: 9,
      anchor: "middle",
      title : "country_iso3",
      padding: 2,
      fill: d => d.country_iso3 === selectedIsoCode ? '#1B98E0' : '#DFDFDF',
      y: "hoursPerDayCombined",
    })),
    Plot.dot(subset, Plot.dodgeX(
      Plot.pointer({
      r: 9,
      anchor: "middle",
      title : d=> getCountryNameByISO3(d.country_iso3) + '\n' + decimalToHoursMinutes(d.hoursPerDayCombined),
      padding: 2,
      fill: '#00C49A',
      tip: "xy",
      y: "hoursPerDayCombined"})
    )),
    Plot.ruleY(subset, Plot.groupZ({ y: "median" }, {
      y: "hoursPerDayCombined",
      stroke: "#393939",
      strokeWidth: 2.5,
      strokeDasharray: [5, 5],
    })),
  ],
});

function voronoiMap(hoveredCategory) {
  //const svg = d3.select(DOM.svg(width + margin.left + margin.right, height + margin.left + margin.right));
  // svg
  //    .append("rect")
 //     .attr("width", "100%")
//      .attr("height", "100%")
//      .style("fill", "white");


const svg = d3.create("svg")
.attr("viewBox", [0, 0, width + margin.left + margin.right, height + margin.left + margin.right])
.attr("style", "max-width: 100%; height: auto; height: intrinsic; background-color:transparent;");
  
  const voronoi = svg.append("g").attr("transform", "translate(" + margin.left + "," + margin.top + ")");
  const labels = svg.append("g").attr("transform", "translate(" + margin.left + "," + margin.top + ")");
  const hour_labels = svg.append("g").attr("transform", "translate(" + margin.left + "," + margin.top + ")");
  
  let seed = new Math.seedrandom(20);
  let voronoiTreeMap = d3.voronoiTreemap()
    .prng(seed)
    .clip(ellipse);
  
  voronoiTreeMap(category_hierarchy);
  colorHierarchy(category_hierarchy);
  
  let allNodes = category_hierarchy.descendants()
    .sort((a, b) => b.depth - a.depth)
    .map((d, i) => Object.assign({}, d, {id: i}));
  
  let hoveredShape = null;
  
  voronoi.selectAll('path')
    .data(allNodes)
    .enter()
    .append('path')
    .attr('d', d => "M" + d.polygon.join("L") + "Z")
    .style('fill', d => d.parent ? d.parent.color : d.color)
    .attr("stroke", "white")
    .attr("stroke-width", 0)
    .style('fill-opacity', d => d.depth === 2 ? 1 : 0)
    .style('opacity', d => hoveredCategory === null || d.data.Category === hoveredCategory ? 1 : 0.5) // Use hoveredCategory variable
    .attr('pointer-events', d => d.depth === 2 ? 'all' : 'none')
.on('mouseenter', function (d) {
  // Select the current element and set its opacity to 1
   d3.select(this).style('fill-opacity', 1);
   d3.select(this).style("cursor", "pointer"); 

  // Set opacity to 0.5 for all other elements
  voronoi.selectAll('path')
    .filter(e => e.id !== d.id)
    .style('opacity', 0.4);

  // Other actions you want to perform on mouse enter
  let label = labels.select(`.label-${d.id}`);
  label.attr('opacity', 1);
  let hour_label = hour_labels.select(`.label-${d.id}`);
  hour_label.attr('opacity', 1);
})
.on('mouseleave', function (d) {
  // Select the current element and restore its original fill opacity
  d3.select(this).style('fill-opacity', d => d.depth === 2 ? 1 : 0.5);

  // Restore opacity to 1 for all other elements
  voronoi.selectAll('path')
    .filter(e => e.id !== d.id)
    .style('opacity', d => d.depth === 2 ? 1 : 0);

  // Other actions you want to perform on mouse leave
  let label = labels.select(`.label-${d.id}`);
  label.attr('opacity', d => d.data.hoursPerDayCombined > 0.8 ? 1 : 0);
  let hour_label = hour_labels.select(`.label-${d.id}`);
  hour_label.attr('opacity', d => d.data.hoursPerDayCombined > 0.8 ? 1 : 0);
})
.on('click', d => {
    mutable clickedSubcategory = d.data.key || d.data.Subcategory; // Store the label's value in clickedCategory

    // Find the target div with class "beeswarm"
    const targetDiv = document.querySelector(".beeswarm");

    // Scroll to the target div smoothly
    if (targetDiv) {
      targetDiv.scrollIntoView({behavior: 'smooth' });
    } else {
      console.log('Target div was not found.');
    }
  })
    .transition()
    .duration(500)
    .attr("stroke-width", d => 7 - d.depth*2.8)
    .style('fill', d => d.color);

  //category labels
  labels.selectAll('text')
    .data(allNodes.filter(d => d.depth === 2 ))
    .enter()
    .append('text')
    .attr('class', d => `label-${d.id}`)
    .attr('text-anchor', 'middle')
    .attr("transform", d => "translate("+[d.polygon.site.x, d.polygon.site.y+6]+")")
    .text(d => d.data.key || d.data.Subcategory) 
  //  .attr('opacity', d => d.data.key === hoveredShape | d.data.hoursPerDayCombined > 0.8 ? 1 : 0)   
    .attr('opacity', d => (hoveredCategory !== null && d.data.Category === hoveredCategory) || (hoveredCategory === null && d.data.hoursPerDayCombined) > 0.8 ? 1 : 0)
    .attr('cursor', 'pointer')
    .attr('pointer-events', 'none')
    .attr('fill', d => d.data.Category === 'Experience oriented' ? 'white' : 'black')
    .style('font-family', 'Oswald')
    .style('text-transform', 'capitalize')
    .style('font-size', d => d.data.hoursPerDayCombined > 3 ? "30px" : d.data.hoursPerDayCombined > 1 ? "20px": d.data.hoursPerDayCombined > 0.8 ? "16px" : "14px");


  //hours combined labels
  hour_labels.selectAll('text')
    .data(allNodes.filter(d => d.depth === 2 ))
    .enter()
    .append('text')
    .attr('class', d => `label-${d.id}`)
    .attr('text-anchor', 'middle')
    .attr("transform", d => "translate("+[d.polygon.site.x, d.polygon.site.y+25]+")")
    .text(d => decimalToHoursMinutes(d.data.hoursPerDayCombined))
    .attr('opacity', d => (hoveredCategory !== null && d.data.Category === hoveredCategory) || (hoveredCategory === null && d.data.hoursPerDayCombined) > 0.8 ? 1 : 0)                                
    .attr('cursor', 'pointer')
    .attr('pointer-events', 'none')
    .attr('fill', d => d.data.Category === 'Experience oriented' ? 'white' : 'black')
    .style('font-size', d=> d.data.hoursPerDayCombined > 0.3 ? '14px' : "12px")
    .style('font-family', 'Roboto Condensed');
  
  return svg.node();

}

voronoiMap = ƒ(hoveredCategory)

db = DuckDBClient.of({ 
    tt_ghd_countries: tt_ghd_countries,
    tt_countries: tt_countries
})

db = DuckDBClient {}

query = db.sql`SELECT
  Category as category,
  SUM(CASE WHEN country_group = 'USA' THEN hrs ELSE 0 END) AS country_stat,
  AVG(CASE WHEN country_group = 'World Avg' THEN hrs ELSE 0 END) AS global_stat
FROM (
  SELECT
    country_iso3
    ,CASE WHEN country_iso3 = ${selectedIsoCode} THEN 'USA' ELSE 'World Avg' END AS country_group,
    Category,
    SUM(hoursPerDayCombined) as hrs
  FROM tt_ghd_countries
  GROUP BY 1,2,3
) a
GROUP BY Category
  ORDER BY 2 desc;
`

query = Array(8) [Row, Row, Row, Row, Row, Row, Row, Row, schema: Array(3)]