Dream Corps / Empathy Network SQL

I signed a “I won’t do bad things” form a bit back and now I have access to the DC email db for the purposes of helping them write better SQL queries for internal reporting- right now there are lot of UI-generated queries, which aren’t as powerful as they’d like.

On the scale of “crushing responsibilities for other people’s private information” I have had, this is fairly light, but all PII is a big deal and I don’t forget that for a minute.

How many users are on an email list with a certain tag name (ish)

select core_tag.name, count(distinct core_user.id) as user_count
from core_user
         join core_subscription on core_subscription.user_id = core_user.id
         join core_action on core_action.user_id = core_user.id
         join core_page_tags on core_page_tags.page_id = core_action.page_id
         join core_tag on core_page_tags.tag_id = core_tag.id
where core_subscription.list_id = 11
-- and core_tag.name in ('name1', '...')
group by core_tag.name
order by user_count desc

This is fun because digging into a new db is always fun. It’s my first time using Asana and it’s been painless so far.

The data lives in ActionKit which I haven’t worked with before but it’s plain mysql so it doesn’t really matter- although there is a “reports” UI which seems a bit like a cross between Scratch and pandas.

public DC actionkit db docs

One of the requests was “SQL language to pull tags that contain certain characters (ex. “@” for audience reporting)” which, unless I’m misunderstanding something, is… absurdly simple, just not functionality offered by the drag-n-drop query builder.

select distinct name from core_tag where name like '%@%' order by name

One of the best queries to run in a db you haven’t worked with before, when you don’t have actual db connection so you can’t properly see all the tables at once:

select COLUMN_NAME
from information_schema.columns
where table_schema = 'ak_dcorps'
order by table_name, ordinal_position

Leetcode

I felt fragmented and distracted so I did some “easy” leetcode problems (still sometimes a pain if you’re not in practice!) to settle my brain before digging into bigger projects. I still think that leetcode style problems are not how I personally would prefer to evaluate candidates, but the easy ones are soothing to do. I worked in ruby, since that’s the language of my next job (and also I just really like it- although I do slip into python syntax sometimes and have to rewrite, oops)

Laptop setup (ad hoc)

Installed spotify

Set up a few more chrome profiles (a full setup will be ~10 but I don’t need most of them often)

Setup global gitignore git config --global core.excludesfile ~/.gitignore

Watched the 1st 2020 prez debate

Not really code, but not really life-for-fun either. Worth mentioning. I watched the whole dang thing and some commentary afterwards. Predictably, it tanked my productivity for hours.

Grad school? Yikes, nah

I attended the Virtual Idealist Grad School Fair and chatted with a few colleges. The process of getting grad school funding feels a lot like the process of trying to do taxes or buy a house- it requires a lot of mysterious knowledge and expertise which you struggle through and then regret. Anyway, I’m not seriously interested in grad school right now. Finding a professor to be your PhD advisor (with whom you would… suit) seems like a Very Hard Problem.

Python and data

I lurked Visualize It! Using Python to Bring Data to Life the mapping libaries portion was most interesting especially the downloading maps to then put data onto (I’ve used pandas for work so that part wasn’t new)

Management and Merit

I had my first ever get-merit call. It was really good! I have a whole blog post that needs to get edited into shape and posted about it. Basically I was gifted with 45 minutes of time with a person who does exactly what I want to do in my career- engineering management while still staying technical.

Some snippets, unformed and unexplained

Various personal stuff

Lots of inbox 0 -ing, unsubscribing from emails, fix weird contacts records, cancel Tuple (again), personal finance stuff, reply to ppl and alumni-related stuff, biking!!!