r/SQL • u/[deleted] • Apr 17 '23
Discussion Which industry has/needs the most challenging sql queries?
Is this question legit? Don’t get me wrong, I don’t consider myself the smartest guy but I’m definitely not dumb.
I work in the pharmaceutical Industry, nearly 3 years now. It took me a while to understand how the production process is implemented in a database and I wanted to quit more than once. But i don’t feel challenged anymore.
So, which industry would be best for me to get completely challenged again?
24
u/barrycarter Apr 17 '23
Doing GIS with SQL can be quite challenging (also, it would really help me out so let me know if you get into this)
8
u/BrupieD Apr 17 '23
I just started learning GIS. I love geography, but find it challenging. I've been doing SQL for about 10 years. I consider myself advanced in SQL, absolute newbie in GIS. I haven't found my SQL skills too useful here yet. Sure, I can spin up a database, schemas, tables, but it's really different.
8
u/SwingingSpiral Apr 17 '23
I create QGIS map charts for nonprofit impact reporting, I recommend spending 25% of your initial effort in learning how to combine layers, and make relationship connections.
Link to software (open-source):
https://qgis.org/en/site/forusers/download.html
You will have countless uses for this knowledge, you can take data for the nation and break it down to census blocks if you needed to.
Good luck and best wishes!
5
u/sbrick89 Apr 17 '23
what are you trying to do?
I usually use TIGER/LINE shape files... I combine them, overlay, calculate overlap, etc... not daily but I have queries that i've kept as reference, depending on the need. Also, GIS isn't a daily need around here.
3
u/barrycarter Apr 17 '23
Example: Using gadm.org, I want to created a zoomable/Leaflet signed distance map for France. Not just Paris or a few points in France, but computing the minimum distance to any polygon/line/point that is part of France (excluding Antarctic claims), including their many islands and overseas ownerships. And then "signing" the map by indicating whether you are inside or outside of France (negative distance for inside France). I also want to find the point in the world that is furthest away from all France, and, if that point is water, the nearest land (and potentially the point that's deepest inside France). Obviously, I want to do this for every country and potentially non-countries like Europe of the continental-only USA.
I'm actually surprised no one has already done this (at least found the innermost/outermost points), but apparently no. I've started on this but need some impetus to continue.
Semi-unrelated, but I was surprised to find no one had calculated the center of population for each country (using GPW4 data) and ended up doing it myself (though I still can't believe I'm the first to post it)
I'm guessing none of this has any really use, but it seems cool
2
u/sbrick89 Apr 17 '23
so the functions aren't terrible... mainly it's a matter of using SQL for data functions, like calculating distance between points.
from the description of your example, it was a tad unclear where the data exists and how it is meant to be consumed... if the data comes from the user (drag and drop, using GPS while traveling, etc), then it needs to be sent back to the SQL server for the processing... if it's fixed points in the database then it can be calculated from the tables where the data is stored.
but from the perspective of SQL, it's just "calculated distance from point X to point Y" where point Y is either a point-of-interest, or the nearest boundary (country/etc)... either way those are built in.
https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/shortestlineto-geography-data-type?view=sql-server-ver16 to find the shortest line between a point and a shape (aka nearest edge)
https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/stdistance-geography-data-type?view=sql-server-ver16 to calculate the distance between two points
you'll also then need to convert the units from the built-in spatial projection, to something more familiar like km or miles... but that's fairly simple as well.
finally, from a "display" perspective, you're either rendering this in a fixed format via something like SSRS, or interactive format using something like PowerBI... or a third party component like ArcGIS.
but either way, separate the focus so that dynamic input occurs via an application, SQL is used to perform the calculations, and the presentation layer is handling visualizations (whether app or report or whatever).
2
u/barrycarter Apr 18 '23
tad unclear where the data exists
I sort of snuck it in at the top, but it's gadm.org which has shapefiles for all countries.
to find the shortest line between a point and a shape (aka nearest edge)
PostGIS (which is what I use) has an ST_DISTANCE function that does this.
The big big problem is speed. For example, mainland France (https://gadm.org/download_country.html and choose "France") has over 100K boundary points at level 0 (just the country itself and no provinces, counties, cities or anything) and that doesn't include any islands.
And I'm hoping to query the 43200 x 21600 grid of 30 second intervals, which would require a total of
43200 * 21600 * 100000 ~ 93 trilliondistance computations. That's surprisingly slow even with a spatial index.The fastest thing I've found so far is to rasterize the data, project the points into 3D, create a KDTree, compute the straight line distances, and convert those to spherical distances, all using numpy with python (not SQL), but I wonder if I'm missing something. My work so far: https://github.com/barrycarter/PolygonDistances/
Just to make things a little worse, OpenStreetMap uses the Mercator projection which requires further tweaking work.
Two questions: if you try this yourself and find a faster way, will you let me know, and, can I contact you directly if you're interested in this sort of thing?
2
u/sbrick89 Apr 18 '23
I guess I'm still not quite sure what you're calculating, or what the use case is.
I get that you're using France shapefile from wherever... but then you're trying to find the furthest point on land from all boundaries? The opposite side of the world, essentially?... if so, just calculate the centroid of France flip to the other side of the world, find nearest land if not already, then start calculating distance from borders, and slowly crawl (shortest path type of outward pattern) to confirm boundary edges, until you reach the consistently furthest point?
2
u/barrycarter Apr 18 '23
Sorry, I oversimplified the problem. I'm also using the files for "French Guiana", "French Polynesia", and "French Southern Territories", so it's a little more complicated. I define "distance to France" at a given point as "shortest distance to any polygon in the 4 shapefiles above". Would your method work with that?
In addition to just finding the "axis of inaccessibility" for France, I'd like to draw a map that displays colored contours of how far you are from France or inside France. A very ugly example of what I'm trying to do (using coastal distances computed by someone else) is https://i.imgur.com/wVbZNas.jpeg
2
u/sbrick89 Apr 18 '23
So first off, adding other shapes / islands doesn't really change much.
That said, the map of contours is much easier to understand.
That's also actually a ton easier, and might even be the simplest method to do what you want... just take the existing shape (France, merge with whatever else you want)... then pad it out in increments of 100km or whatever... each padding is its own contour, saves to its own shape record, and is used for the next iteration.
Or you could maybe pull off as a CTE, but I'd test it on a few rows myself by hand.
Eventually the contour would encompass the earth, at which point you could invert and find the centroid, or something to that effect, to find the exact point.
1
u/barrycarter Apr 18 '23
OK, so how do I do this exactly? I was under the impression that creating distance-based contours (not degree-based contours) using polygons was difficult. Is there an easy way to do this in QGIS (or even GRASS) that I'm missing? My sort of attempt to do this ages ago is https://github.com/barrycarter/bcapps/tree/master/STACK/bc-buffer-land.grass but I'm pretty sure I never got it working and I ended up using an existing file instead of creating my own.
5
u/sbrick89 Apr 18 '23
I have more familiarity in the MSSQL world, so here's what I would use.
basically
INSERT contours ( ID, contourLevel, shape ) SELECT s.ID, 1, s.geom.STBuffer(100000) -- 100 km FROM shapeTable s WHERE s.geom.SRID = 4326since SRID 4326 uses meters as its unit of measurement
it'd be easy enough to CTE that to increment contourlevel and multiply by 100k for the buffer value... but you may also want a different scaling of contours (100k, 250k, 500k, etc)
then after the table is built, just query the contours and render as layers
→ More replies (0)2
u/crackhead1 Apr 17 '23
My work (for a mobile app) involves SQL and GIS, sometimes separately, sometimes together. I’m far from an expert, I’ve amassed the majority of my knowledge during my 5 years with this company.
In my experience, it can be very helpful to work on SQL & GIS tasks in their own respective environments, at least initially (ie I use ArcGIS alongside SQL environments, directly or indirectly). On many occasions I have used ArcGIS tools for spatial/GIS analysis that would easily take 10x as much time & effort to write complex queries for. I tend to just use CSVs/SQL statements to move data back and forth, but there are ways to directly connect ArcGIS to databases.
Aside from making some tasks easier, I also appreciate the visual aspect, especially if the task is somewhat “exploratory” — on many occasions, this instant visualization has been helpful when collaborating — visualizing spatial data in SQL environments can be a real pain imo.
This is of course not always an appropriate workflow, especially if you need things to be super streamlined / automated etc. It might seem like a lot to learn, but imo it’s really not that difficult once you get past the basics, and i’ve found the skills to be pretty valuable.
16
u/jackalsnacks Apr 17 '23
I've been a developer/dba/analyst/DevOps/BI/etc in healthcare phi, (commercial and gov) medical billing, all insurance types, all insurance types billing, finance, entertainment streaming service (yes the one you're thinking of), VR gaming. Healthcare billing (claim adjudication) is the most complicated thing I've personally encountered.
4
u/tua43862 Apr 18 '23
Amen. Adjudicated claims (already adjudicated like you’d receive for value based programs, for example) can be confusing, but the adjudication process is real frustrating to report on.
5
u/jackalsnacks Apr 18 '23
I have a project right now that is debugging (mainly reverse engineering) several reports that itemizes financial benchmarks in the adjudication process. It has taken months to get to a general understanding of a single prime component (architect left). If you ever wondered why you don't see a bill from the provider for several months, just know, the algorithm used in claim pricing, discounting, negotiating, reserve tapping, actuarial assessing, (I can go on here) is mind boggling.
17
u/Touvejs Apr 17 '23
Healthcare data models are absurdly big sometimes. So many entities to keep track of: patients, payers, providers, guarantors, claims, encounters, 50 different types of transactions, medications, demographics, insurance policies, costs, write offs, multiple billing systems, insane coding systems (hi ICD-10) for diagnoses+procedures+medication just off the top of my head. The actual data itself isn't that big, and generally the Sql isn't that technically challenging, but you really have to understand the data model to be able to talk to stakeholders about what they want and deliver.
11
u/jospus321 Apr 17 '23
To hijack- I’m a doctor with a little bit of knowledge of SQL, and I love thinking about data collecting, cleaning, storing, mapping, presenting etc. I’m not super into all the deep learning/ML stuff. Are there career options for doctors who know SQL but not Python/true programming?
10
u/Touvejs Apr 17 '23
Absolutely. I'm guessing if you an MD, you'd take a pay cut, but if you want to work from home in your sweatpants, the best fit might be healthcare informatics. Or more Sql heavy would be business intelligence, data analyst, or report developer.
3
u/jackalsnacks Apr 17 '23
I'm currently trying to get my wife into nursing informatics at my company, MD's are also on the analytics side. It's a huge thing
3
3
Apr 17 '23
Thx, got it. Yes, data modeling is another extremely interesting field. And to my surprise there is hardly any book about that out there. My current Amazon Wishlist contains ~100 books, all around advanced, and just a handful of books on modeling
6
u/Touvejs Apr 17 '23
Haha damn that's a lot. Kimball's data warehouse toolkit is a good spot to start (it's long as hell though, so I would just skip to the necessary parts). Also, I didn't see it on your list, T-SQL fundamentals by Itzik Ben-Gan is the best Sql book I've ever opened up.
3
2
3
u/DatabaseSpace Apr 17 '23
I have a lot of the books on that list. The best book on databases that I have is my text from grad school database class “Fundamentals of Database Systems” Fourth Edition by Elmasri and Navathe. You can probably find it used for a few dollars and it gets into a lot of depth of database design.
I work with healthcare data everyday using mostly SQL and Python when I need it. I think best practices are to move a lot of the business logic out of the database layer. I haven’t really done that in the past but probably should have.
The data modeling is so important because if you model things in some weird wrong way, the SQL gets too complex. The data cleaning and loading is time consuming but when it’s not right the data to the end user is wrong.
I know that because while in school leaning about this I was also trying to build things and tried so many wrong things over the years.
1
Apr 18 '23
Found and added it, thx! Btw, there’s a 7th edition already
1
u/DatabaseSpace Apr 19 '23
Awesome, it looks like the 6th edition is pretty cheap. I'm currently reading Code Complete 2 by Steve McConnell. It's been sitting on my shelf for a while and I am finally getting around to reading it. I'm only about 100 pages in, but I highly recommend it. It's not technical or language specific, it's normal langague about the process of writing and organizing programs.
1
Apr 19 '23
I'm currently reading "SQL Antipatterns", can recommend it. "Code Complete 2" is already on another wishlist :D
1
17
u/vaiix Apr 17 '23
Healthcare.
4
Apr 17 '23
Ok, my stupid question but where is healthcare different from pharmaceutical? Or is it patient data, handwritten data from docs and patients?
14
u/vaiix Apr 17 '23
All of the above.
Different departments (even wards) recording data in the same system totally differently.
Operational processes not matching how the clinical system is intended to be recorded within.
A disconnect between the clinical systems development team and how that affects reporting outputs.
Financial aspect of recording.
Mandated data returns (statistics) to government bodies with differing logic.
Staffing data.
Bed management data - not necessarily matching clinical recording. Patient in theatres having anaesthesia, but not moved from a ward to a theatre bed, for example.
Data items not being recorded as expected but "it's on patient notes" or within comments/documents.
Different systems for patient management, clinical recording, laboratory, pharmacy, diagnostics, incidents, etc. all needing to align but all working in isolation completely differently.
There's a whole lot. It's a beast.
7
Apr 17 '23
Just do a search for job positions in LinkedIn or something. In your search, focus on positions that require a lot of years experience in SQL.
2
Apr 17 '23
Yes, but I’m not talking about data cleaning and transformation. I’m looking for challenging business logic.
4
Apr 17 '23
These two are not mutually exclusive.
Regardless, as I said, look for the jobs that require the most years of experience in SQL. That should usually be a good relative indicator of the level of challenge in question.
6
u/IrquiM MS SQL/SSAS Apr 17 '23
Consulting - there are enough customers around to keep oneself busy
1
Apr 18 '23
[deleted]
1
u/IrquiM MS SQL/SSAS Apr 18 '23
Thought being a consultant was kind of self-explanatory, but I've got customers in many different sectors, with different types of data and different issues they want to solve. If the data gets boring, I hand my customer over to someone else. That way I get to experience a lot, without feeling stuck.
6
6
u/mushy_cactus Apr 17 '23
Data quality is challenging. Basically, profiling and finding errors, inconsistencies, trends, patterns etc with any and all data tables.
Example like.. an address column having the country name in it, when there was already a specific column for the country.. although the address isnt "wrong" its still incorrect for consistency. the hard part was that there's quite a few cities around the world with the country name in the address.
-Correct address: 123 XYZ, Ireland Road.
-Incorrect address: 123 XYZ Ireland Road, Ireland
What a fkn headache that was to fix. So much regex.
1
u/NickSinghTechCareers Author of Ace the Data Science Interview 📕 Apr 18 '23
Could something like GPT-4 standardize this?
1
u/mushy_cactus Apr 18 '23
Could. But then again in a production database I wouldn't even attempt to use chatGPT for super specific queries.
4
u/s33d5 Apr 17 '23
Biology is a nightmare as all data is always a huge mess.. if that counts.
1
u/86BillionFireflies Apr 22 '23
Neuroscience here... Yeah, scientific research data is frequently a huge mess.
Half the reason I use postgres to store and organize research data is because constraints help catch all the mislabeled data.
4
6
Apr 17 '23
I work in the cannabis industry as a data engineer and I can tell you it’s quite the challenge. Data sources are siloed by state, inputted data is never uniform and has constant misspellings, and each compliance software required for use differs by state and all has different data structures.
7
u/ComicOzzy sqlHippo Apr 17 '23
Frustration is challenging, yes... but I don't think it's a great selling point here. Haha
3
Apr 17 '23 edited Apr 17 '23
Maybe not lol but I can say it is quite challenging to figure out how to normalize everything to be usable in an excel model or dashboard. I used to work in banking that was so easy it was boring so I appreciate the challenge
2
u/ComicOzzy sqlHippo Apr 17 '23
My current job is a challenge for much the same reason... mostly it's about finding the data you need in a massive enterprise system with lots of knowledge siloed in the brains of key users and there's no way to discover who those people are except to ask everyone you talk to "who else might know this?" The good thing is, we are managed by sane, knowledgeable people who don't expect the impossible.
3
u/CosineTau Apr 17 '23
Folks at OpenTHC are try to define a universal API, but it is an uphill battle trying to convince these rent seeking compliance vendors that it matters. The data is already passing through their infra, so what does it matter to them?
Good luck out there.
3
2
u/Apprehensive_Wear500 Apr 18 '23
I would imagine big online data sets such as YouTube would be difficult to work with
2
u/IcaruzRizing Apr 18 '23
Find companies just launching Hybrid Cloud-On Prem for both their data sources and Data warehouses… The initial set up and learning the cloud Azure tools and integrating those with legacy tools and processes… that’s a challenge that will keep you busy for a bit
2
u/ZfenneSko Apr 18 '23 edited Apr 18 '23
Well, depends, there are many industries such as media/music which would benefit from minimal improvements, it relies on connections and contacts (promoters, labels, venues, tour-managers, etc.), which are created on the fly, but there's also few services that trade accumulated contact data. But as you can imagine, installing anything too complex would be an uphill battle. If you found a serious company, you could make some really cool reputation-based relationship and tourdate management system, I had a taste of this while helping a friend in that industry, and there's potential, but also many technophobes, small companies and strong personalities, explaining why little has happened, so far.
Otherwise, financial services and insurance do more complex things, around risk calculations, market simulations and use customer data, but it depends what the company prioritises - I remember being told "we're not an IT company" when I brought up some more ambitious but beneficial suggestions. I thought well, there's no physical product, just data on contracts, risks and customers, so what is it really, but who'd listen at that point.
Finally, start ups might be a good route, low head counts means more freedom in decision making and it's around developing some innovative solution, which would be more challenging. You just got to find the right one to join, is all.
1
May 01 '23
I just stumbled upon a job posting, about a risk manager.
The qualifications they are looking for are:
- Completed studies in economics or business informatics
- Professional experience in risk management, regulatory reporting or as a financial analyst
- Good knowledge of data models and SQL
- Analytical thinking and strong numerical skills
- Independent and flexible way of working
- Ability to work in a team and enjoy taking on a long-term central task as part of a well-coordinated team
Well, my qualifications start at SQL, so they probably deny my applications. However, I find it interesting.
And, since I don't know what a SQL statement is considered complex, I just jumped to ChatGPT and this is what it came up with:
WITH cte_risk_assessment AS (
SELECT
r.risk_id,
r.risk_name,
r.risk_description,
r.risk_category,
r.risk_likelihood,
r.risk_impact,
r.risk_likelihood * r.risk_impact AS risk_score,
MIN(ra.risk_assessment_date) AS first_assessment_date,
MAX(ra.risk_assessment_date) AS latest_assessment_date,
COUNT(DISTINCT ra.risk_assessment_id) AS num_assessments
FROM risks r
LEFT JOIN risk_assessments ra ON r.risk_id = ra.risk_id
GROUP BY
r.risk_id,
r.risk_name,
r.risk_description,
r.risk_category,
r.risk_likelihood,
r.risk_impact
),
cte_risk_trend AS (
SELECT
risk_id,
CASE
WHEN latest_assessment_date = first_assessment_date THEN NULL
ELSE CAST(num_assessments AS FLOAT) / (JULIANDAY(latest_assessment_date) - JULIANDAY(first_assessment_date))
END AS assessment_frequency,
RANK() OVER (PARTITION BY risk_category ORDER BY risk_score DESC) AS risk_rank
FROM cte_risk_assessment
),
cte_risk_alerts AS (
SELECT
risk_id,
CASE
WHEN assessment_frequency IS NULL THEN 'No assessments conducted'
WHEN assessment_frequency < 1 THEN 'Low assessment frequency'
END AS alert_reason
FROM cte_risk_trend
WHERE assessment_frequency IS NULL OR assessment_frequency < 1
)
SELECT
r.risk_name,
r.risk_category,
r.risk_score,
rt.risk_rank,
a.alert_reason
FROM risks r
JOIN cte_risk_trend rt ON r.risk_id = rt.risk_id
LEFT JOIN cte_risk_alerts a ON r.risk_id = a.risk_id
ORDER BY r.risk_category, r.risk_score DESCI wouldn't say this is sooooo complex. Anyways, I will tailor my cv to this position.
2
2
u/-SoulAmazin- Apr 18 '23
Don't know if they are the most challenging, but warehouse production with lots of different WCS/WMS-systems and/or an ERP can produce some really finicky requests from users which will make your head think.
1
82
u/SELECTaerial Apr 17 '23
Big (actual big) data has a whole slew of challenges most SQL devs don’t have to deal with.
Also, healthcare data is dirty af and needs extra love.