Direct link to Fletcher Training folder: https://tufts.box.com/s/v2lx622whvqa3lq9tgidmp1imfpcxcnn
Fletcher SQ Training #1 9/4/2024
Introductions
1:50 overview of what FGI is doing/goals
3:30 wants people to be interactive; has standard examples, but wants to use real examples
4:21 Bases/person v.application
10:00 one to one and one to many relationships
10:30 related records
17:45 sample build, everyone who has a rank 1 address in MA
22:40 different types of exports and how they are grouped
24:30 adding rank 2 address
25:37 deleting exports using ctrl-click and drag
28:00 adding filter for rank 2 address in MA
30:30 filters are automatically 'and'
32:00 sample build, applications from people whose rank 1 address is MA
24:30 base join
41:00 direct versus extended exports
recommendation to keep extended exports turned off, and do joins directly to the query overall
50:00 direct join
53:00 one row per base
Fletcher SQ Training #2 9/11/2024
1:50 review of address build
7:00 determination of table ranks https://knowledge.technolutions.net/docs/database-structure-determination-of-table-ranks
10:30 renaming joins to prevent confusion
14:00 adding 'or' in filters
15:50 old STL 'active' address is the same as #1 rank overall
16:45 application by population base
18:00 sample query, application base: finding applications from people who went to Tufts before (rank 1)
26:00 subquery: looking for Tufts across all previous schools
31:50 check logic button
33:00 (the problem of) base joins to multiple schools
36:00 adding sorts and filter to that base join
39:00 one row per school
44:30 different types of bases
46:30 sample query school base
50:00 sample query form responses
53:00 join to form
53:45 question looking for the first form response
Fletcher CJ Training #3 9/20/2024
1:00 Troubleshooting: why is US missing in ist list of countries in exports--in STL exports, Tristan showed how US is hard coded to return null--may have been custom coded for Fletcher
8:00 review of CJ version of that query
12:00 using a subquery to find all of the enrolling students
14:45 direct v. extended filter reminder
16:15 using <> with dates
16:55 relative dates (today - x) days are default; weeks, years, fortnights ?! but probably not months
18:45 subquery export from applications
19:40 concatenate output
21:30 form responses, starting with people
22:50 adding a sort
23:40 adding details of which form
24:40 limiting which forms are returned
29:30 adding subquery export of other forms returned
36:50 Origin Sources overview
44:00 Sample query on Origin Sources
47:45 existence export
48:30 system query : one row per origin source group name, or origin group source type
--> Tristan off in the weeds satisfying his own curiosity: independent subqueries, reviewing the SQL in a custom SQL base, trying to see what he can get attached back to person for origin sources
but does eventually come back round to doing a count of people who have each type of origin source
...