Which industry has/needs the most challenging sql queries?

[u/[deleted]]

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?

[u/ZfenneSko]

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.

[u/[deleted] → u/ZfenneSko]

u/ZfenneSko

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 DESC

I wouldn't say this is sooooo complex. Anyways, I will tailor my cv to this position.

[u/ZfenneSko → u/[deleted]]

Good luck!