...
51:50 different types of decision by rank joins
Fletcher CJ Training #5 10/11/2024
We discussed a number of decision examples, comparing how we could pull in the most recent decision or use a subquery filter to check across all of a person's decisions, and what a subquery export would look like if we wanted to pull a comma-separated list of all their decisions. We also looked at how we could use subqueries to check for the existence of another application for the same person, where that other application had a deny decision. Finally, we looked at how we could use comparisons in subquery filters to compare various dates, like the first event date vs. the app submitted date.
00:35 sample build: finding admitted applicants
2:30 decision by rank, decision by rank confirmed, decision by rank released, decision by rank reversed
6:00 subquery filter within decision
8:00 no default sort on base subquery joins (doesn't matter for decisions, usually, but does matter for other things)
9:25 most recent sort: descending order
14:20 concatenate list of decisions
19:00 existence subquery filter
25:00 subquery versus base join
32:15 sample build: has previous application with deny decision
35:30 count of apps > 1
40:00 nesting subqueries to compare to make sure we are not looking at the current app
44:00 date comparisons
Fletcher CJ Training #6 10/18/2024
In this session, we walked through a number of troubleshooting examples where joins were misconfigured or where nuances of EXISTS NOT IN vs. NOT EXISTS IN affected the results. Leigh also asked about the location information that sometimes appears on student timelines tied to message clicks or ping activity; the message track query base does have an export for Region, and we could get at country as well with a formula export. Other location information on the timeline appears to be derived from the [person.login] table, and one of the library exports that FGI added includes the most recent login city.
Fletcher CJ Training #7 10/25/2024
In this session, we walked through the library exports that FGI has set up in the Fletcher instance and also talked about how to create your own library exports for common exports and filters that you'd like to reuse in future queries. As we saw, sometimes library exports and filters can be added without any additional configuration, but in most cases they still expect that you would configure something about them: a tags filter for example might expect that you select the tag name you want to check for, it would just save you the steps of adding joins to get to that point.
Fletcher CJ Training #8 11/1/2024
We explored some more advanced concepts like independent subqueries and closed things out with a discussion of some of the less common query bases.