Advent of TypeQL: Day 6
Happy Advent!
Welcome to our Advent of TypeQL. Each day, we’ll post a small series of TypeQL challenges. There will be 14 days from December 11 to December 24, inclusive, so stay tuned!
It’ll be easiest to work through the days consecutively, since they are designed to incrementally build up an intuition of TypeQL. However, each day will also have a shortcut setup to just get a database for that day’s activity, allowing you to hop in without going through all the previous days challenges if you want.
You’ll want to use TypeDB Studio or TypeDB Console running against a TypeDB Cloud or local instance of TypeDB CE.
Background
Santa was preparing to release his Christmas plans. Finally done, he had just torn down his development and staging environments in preparation for a production launch of his grand Christmas plans…
Unfortunately at that precise moment, all of his Christmas plans, supposedly safely stashed in his git repository, got deleted by a pesky engineer with an overly permissive Github token and a mistyped command. Oh dear!
Santa contacted support and was able to recover some of his plans. However, they are incomplete. We’re going to help Santa get his plans back on track for Christmas, one day at a time!
Setup
If you’ve done Day 5, you can continue without setting anything up – just open up Studio or Console and get going!
If you’re new here, you’ll first want to spin up a TypeDB instance and connect with Studio or Console, and create a new database.
Then, you can get Santa’s recovered database schema by copying the linked schema text into a schema transaction’s query interface in Studio or Console, and then commit (note: by default, Studio auto-commits each query when set to “auto” mode).
Get the initial dataset plus subsequent days’ changes as a data file. Then load it by doing the same (follow link, copy text, paste into Studio or Console), but this time use a write transaction – and make sure you have committed.
At this point, you should have a database ready to go!
Day 6
Let’s have a bit of quickfire fun: data analysis using reductions.
Reduce away
TypeQL supports a variety of reduce operations. We’ve already met count and sum, but let’s try those out again!
Let’s count how many production relations we inserted yesterday.
Answer
match
$p isa production;
reduce $total = count;
Next, let’s add up the total number of presents required by all productions for the elf named Cardamom.
Answer
match
$elf isa elf, has name "Cardamom";
$p isa production ($elf), has quantity-required $required;
reduce $total-required = sum($required);
Alright – now let’s do something new: what is the minimum required-quantity on any production?
Answer
match
$p isa production, has quantity-required $required;
reduce $min = min($required);
Now – what’s the maximum population of any country? Recall that countries connect via demographics relations to population-statistics entities that have a population.
Answers
match
$country isa country;
demographics ($country, $statistics);
$statistics isa population-statistics, has population $population;
reduce $max = max($population);
Let’s expand this query a bit to search for the maximum population of a country within each continent. You’ll want to groupby continent!
Answer
match
$continent isa continent;
location-contains (parent: $continent, child: $country);
$country isa country;
demographics ($country, $statistics);
$statistics isa population-statistics, has population $population;
reduce $max = max($population) groupby $continent;
Ok – now let’s work out the ratio between the largest and smallest populations on each continent! We’ll want to do two reductions at once: min and max! Then take those values and compute a ratio in an appended match clause.
Answer
match
$continent isa continent;
location-contains (parent: $continent, child: $country);
$country isa country;
demographics ($country, $statistics);
$statistics isa population-statistics, has population $population;
reduce $max = max($population), $min = min($population) groupby $continent;
match let $ratio = $max / $min;
Last one for the day – let’s add the mean, std, and median to get a nice set of statistics per continent.
Answer
match
$continent isa continent;
location-contains (parent: $continent, child: $country);
$country isa country;
demographics ($country, $statistics);
$statistics isa population-statistics, has population $population;
reduce
$max = max($population),
$min = min($population),
$median = median($population),
$mean = mean($population),
$std = std($population)
groupby $continent;
match let $ratio = $max / $min;
That’s it! Tomorrow we’ll have a look at using functions to build some other interesting analytics operations.
See you soon!
Tomorrow we’re halfway – Day 7! Now available here!
If you encounter any issues, want to chat, or anything else – feel free to post in our Discord or feel free to email me directly.