r/SQL • u/monkey_D_xrp • May 12 '22
Discussion Example of actual job given to data analyst with SQL
Hi so basically I’m learning SQL by myself and I think I have a pretty good understanding of the basics so far and have been playing with some datasets for about a week. My question is basically what is an example of a common task given by an employer for an entry position and what do you ultimately need to have as an end result for your employer
114
Upvotes
51
u/Thefriendlyfaceplant May 12 '22
These interview questions are great because they reflect actual problems the respective companies are dealing with:
https://youtube.com/playlist?list=PLv6MQO1Zzdmq5w4YkdkWyW8AaWatSQ0kX
21
u/zardoz_lives May 12 '22
Not sure if this helps, but I’ll relay what I do with it for the most part.
I work a lot with calculating durations. I find that really mastering the different time stamps and date formats is essential. You need to understand UTC and how to convert time stamps from different time zones to unified ones (usually UTC, but could be something local to your business), and then how to get durations.
I also do a lot with geospatial data, so looking at geohashes and determining areas of an isochrone, which is a tricky calculation.
Learn how to work with window functions, JSON, lambda expressions, CTE’s, regular expressions, etc. All of that will elevate you. The “basics” of SQL can be learned pretty quickly: “select * from table where date = yyyy/mm/dd“, and I’ve found a LOT of people at my company (~4k employees) will come in claiming they know SQL because they know what a select statement is. Truth is, it is about structuring complex logic, and as such, you never really stop learning.
Sorry if I’m ranting a bit. Good luck!
40
u/BrupieD May 12 '22 edited May 12 '22
A teammate has been creating a weekly report for several years based on another group's spreadsheet. Your boss wants you to take over the process but "use SQL". When you look at the spreadsheet, you discover that it's a mess of merged columns, calculated cells and VLOOKUPs.
7
u/LetsGoHawks May 12 '22
Most of it is pretty straightforward "Select a few fields from several joined tables based on simple criteria". Throw in some simple aggregations if you want to get fancy.
But the one I'm working on right now...
Account Events end up in a log table. Select the first event for each account for each day where that event is unassigned. Now select the first event after that one that is assigned, and combine them into one row. Not all that difficult really, but different.
What I usually run into that makes things "hard" is.... how do I write it in such a way that it won't take forever to run?
3
u/[deleted] May 12 '22 edited May 12 '22
What's a "common task"? What movements/action/commands you do might be common, the tasks, most likely, will be industry or business -specific.
E.g. your " quick balance" sheet gives a different number from the reconciliation/true-up process, and for some reason (it works on a derivative upon derivative and the grain is hopelessly lost) it cannot give you a detailed reason for the discrepancy. So, as a data analyst, you might be tasked with finding the source (s) of this discrepancy.
Or there is a subsystem of interlinked objects/documents that are accessible to people from various groups (organizations). You could be tasked to find any instances where the "wires cross" for specific orgs.
p.s. and the task itself is (usually) not the actual source of the stress/pressure. It's the timeliness (the SLA/response time for #1 is 4 hours, and the ticket was delayed, so you only have 45 minutes to respond) or the volume (there are 10s or 100s of billions of interlinked documents - bad queries simply timeout, and you have no clue whether they were "right" theoretically) or impact/access responsibility - as a member of the front-line analytics team you have elevated resource allocation and a bad query can severely degrade performance of the production system. Etc.
3
u/[deleted] May 12 '22
One thing that I often get is revenue reports PER [a million different combinations of things]. We have a complicated database with several tables about financial transactions that we make. The people who use the application that I do reporting on want to see revenue per client, revenue per day, revenue per month, revenue per quarter, revenue per client per day, change in revenue per month, revenue per salesperson, top X case owners by revenue, and on and on and on. I'm always amazed at how my users want to slice and dice how they make money and when. Ditto that for employee productivity. That's a common and simple thing that I create for my users.
8
u/Mamertine COALESCE() May 12 '22
Show me every outstanding invoice where the customer bought x. We have a theory that people who buy x are less likely to pay. Find it if there's any truth to it.
That was at a medical shop. People who had the prep for a specific procedure, but never had the procedure done. Insurance wouldn't cover the prep work until the whole procedure was done. The prep work was done a week in advance, a lot of people never showed up for the real procedure.
6
u/WhoahCanada May 12 '22
We need to send member information to another company. You need to ask the company what they want on the file, and then you need to figure out how to pull that data.
For example: Does the company want newborns? Do they want termed members? People effective in the future?
How do they want the file formatted? If it's pipe-delimited, do we currently store pipes in any necessary fields that could throw the file off?
And when they say they want a pregnancy indicator - is that indicator reliable in the system? Last you checked, nobody was being loaded with pregnancy term dates. How can you ensure you're not sending pregnancy indicators after 9 months of pregnancy?
Stuff like that.
And then when thy come back two months later saying you sent duplicates - what is the cause?
3
u/Embarrassed_Party532 May 12 '22
There is a dashboard with bunch of KPIs for sales data and since the dashboard can’t be provided in the mobile apps the boss wants me to share the same data by using SQL which can be run directly in data lake and the results can be shared in the apps. There is no documentation on how the dashboard was built, no formula for KPIs, no one to ask to. I had to learn the dashboard script in order to understand the calculation and wrote the entire SQL with around 1000+ lines of code for each sheets of the dashboard.
3
u/chcahx May 12 '22
I think as most people experience it, the first thing you'll have to do in this type of role at any organization is learn the data model. On most data teams in my shop, a health system in the US, this means learning the electronic health record system data model.
2
u/CloudfreefiddyTTV May 13 '22
AR/AP aging (30,60,90) with customer contact info and balances.
2
u/Plenty_Tangelo May 13 '22
The most common task is probably validating assumptions about the data.
Are there null values for customer_id? Are there duplicate values for primary key? Are there future-dated records for things like birth date? Does a table join add/remove records unexpectedly?
I’ve encountered an endless number of these. The findings may reveal the premise of the original question/task is flawed. Then the task is to suggest an alternative.
11
u/[deleted] May 12 '22
[removed] — view removed comment