credburn posted:

In order to do that, I need to first find out what districts are most common. That should be easy. But then I need to find out what actors most frequently appear in the films being rented by those districts. That's where, conceptually, I get lost. I feel like this is the sort of mundane and typical task that will become second nature in a career setting, but right now I find I'm overwhelmed.

ulmont posted:

Assuming that you are doing things in multiple SQL statements, you might consider something like this:

1. Find out what districts are most common. You say this is easy, so I'll assume it is.
2. Find out, in those districts, what films are being rented. Since your statement was "where the most movies are rented", I'm going to assume here that duplicate rentals should count twice here.
3. Find out, in those rentals, what actors appear the most.

nielsm posted:

That ER diagram technically seems to be missing an arrow from inventory to store, but that shouldn't be relevant to the question.

Intermediate questions:
How would you get the actors that appear in the most films?
How would you get the actors that appear in the most films that are in inventory for a specific store?
How would you find the most rented out films for a specific store?
How would you find the single most rented out film for every store?
How would you find the most popular actors for the films rented out for a specific store?

Away all Goats posted:

I got hired for a DB Admin role and I have no idea what I'm doing. Oh god.

abelwingnut posted:

if you're dealing with a small oltp, relatively low traffic database, you probably won't have to do much admin-wise. just reindex now and again, least privilege security, backups, etc. just don't be an ioiot/rock its boat, obviously.

cam definitely get hairier so hopefully that's all.

Away all Goats posted:

I got hired for a DB Admin role and I have no idea what I'm doing. Oh god.

Hughmoris posted:

You got this!

What type(s) of databases are you going to work with? SQL Server? Oracle?

Away all Goats posted:

Thanks. They mentioned it was mostly working with PostgreSQL, which I was honest about and said I had no experience in. I mostly worked with Oracle previously. From what I've seen they're pretty similar.

Generic Monk posted:

For the past couple of jobs now I've been doing ETL stuff with MSSQL. I don't really have any formal training with this stuff so I've pretty much been picking up best practices on the fly and not trying to completely cock stuff up like my colleagues do on the regular.

It's kind of difficult to get people without much knowledge or care for best practices to follow a source control proces without it being braindead simple, so in these jobs I've settled into the routine of 'The create table/procedure/function etc scripts are tracked in Git; if you want to edit an entity in the database you open/edit/run these files so you're not making changes to stuff in the database that then doesn't get recorded in source control'. This works but it's prone to error and feels like a clunky hack.

How do others manage this? It seems like SQL server management studio really isn't remotely interested in the idea of source control or that you would regularly be running external files, but surely you would want to have this stuff in source control. Am I just using the wrong tool? I just found out DataGrip exists which looks really nice, but It's quite possible my workflow is just hosed up.

monochromagic posted:

For ETL stuff I'd recommend looking into dbt. There are many similar tools out there, and some might be better for your use case, but start with dbt. It is by far the largest and most well-supported, and seems to fit your needs, and you can run it open source. Also hooks nicely into CI/CD processes so you don't actually have to do the changes manually.

(As an aside - maybe also look into doing ELT instead, it's what all the cool kids are doing)

Generic Monk posted:

Redshift

monochromagic posted:

but I'm just waffling on about data engineering now and it probably isn't the thread for that.

abelwingnut posted:

we moved our warehose to redshift from snowflake a few months ago. while it is quicker and better structurally, the web ide is loving awful. it'll run queries up to 300k characters, but it won't save queries longer than 30k, which...what the gently caress? and yes, those numbers are correct. it'll run queries it can't save. baffling.

furthermore, it has the dumbest autocomplete/intellisense in the known universe. there's zero in the way of auto-formatting. the lefthand panel containing the schema is a ux nightmare.

oh, and it crashes like every tenth query you run.

don't go to redshift if you can avoid it! at least snowflake wouldn't crash all the time, even if writing in it is hellish.

Average Lettuce posted:

Is there a thread for that?

Hughmoris posted:

Are you an analyst, engineer, or administrator for Redshift? Or a combination of all three?

From the outside looking in, I often hear that Redshift is a platform that can offer a lot of juice but you have to know what you're doing in terms of modeling and writing queries. I've toyed with it on a few personal projects but my job doesn't use it.

Seventh Arrow posted:

I created one years ago, but there was zero interest. The data engineering subreddit is pretty decent, though.

monochromagic posted:

I'd be open to create an OP for a data thread and see if there's interest. I love talking about that poo poo and am currently a one person data engineering army, so having some input would be welcome. Ofc, there might be decent overlap with this thread as SQL is still king in this area, but I feel that there's a decent amount of stuff that's out of scope for people mostly interested in DB management (love you guys and everything you do).

Hughmoris posted:

I think that's a great idea!

I've done data analyst work for a while and really want to break into data engineering. Specifically, I (think) I want to become a Databricks engineer so I've been brushing up on that. Any of yous doing Databricks work?

Seventh Arrow posted:

If you want to break into data engineering, the things you should prioritize the most are:

Python
SQL
Data Modeling
ETL Principals
One of the big 3 cloud technologies

Databricks has kind of evolved beyond pyspark, but being proficient at pyspark should nevertheless help you get your foot in the door at least.

Generic Monk posted:

For the past couple of jobs now I've been doing ETL stuff with MSSQL. I don't really have any formal training with this stuff so I've pretty much been picking up best practices on the fly and not trying to completely cock stuff up like my colleagues do on the regular.

It's kind of difficult to get people without much knowledge or care for best practices to follow a source control proces without it being braindead simple, so in these jobs I've settled into the routine of 'The create table/procedure/function etc scripts are tracked in Git; if you want to edit an entity in the database you open/edit/run these files so you're not making changes to stuff in the database that then doesn't get recorded in source control'. This works but it's prone to error and feels like a clunky hack.

How do others manage this? It seems like SQL server management studio really isn't remotely interested in the idea of source control or that you would regularly be running external files, but surely you would want to have this stuff in source control. Am I just using the wrong tool? I just found out DataGrip exists which looks really nice, but It's quite possible my workflow is just hosed up.

Seventh Arrow posted:

If you want to break into data engineering, the things you should prioritize the most are:

Python
SQL
Data Modeling
ETL Principals
One of the big 3 cloud technologies

Databricks has kind of evolved beyond pyspark, but being proficient at pyspark should nevertheless help you get your foot in the door at least.

abelwingnut posted:

engineer, but they're trying to shoehorn me into a bunch of analysis and i'm not liking it!

Hughmoris posted:

Do you know why the company switched off Snowflake? It seems the biggest reason I hear about is Snowflake is pretty drat pricey.

RandomBlue posted:

python? you mean the money laundering language?

monochromagic posted:

I'd be open to create an OP for a data thread and see if there's interest. I love talking about that poo poo and am currently a one person data engineering army, so having some input would be welcome. Ofc, there might be decent overlap with this thread as SQL is still king in this area, but I feel that there's a decent amount of stuff that's out of scope for people mostly interested in DB management (love you guys and everything you do).

monochromagic posted:

mortarr posted:

A lot of Data Engineering jobs mention python - where should I start? I've done a lot of data related dev stuff, almost all on c#/ms SQL if that helps? Looking at one of the Azure certs mentioned above too.

credburn posted:

Hey gang, I am struggling to join these tables and produce the results I'm looking for. I'm using postgres and this diagram:



This is a slight variation from my earlier goal; now what I am trying to accomplish is, I want to find out what the five most common districts are, and in those I want to produce what genre is most popular in each of the five districts.

I am able to get the five most common districts pretty simply:

code:

SELECT district, COUNT(*) as district_count
FROM address
GROUP BY district
ORDER BY district_count DESC
LIMIT 5;

Now I want to use that data in combination with the genre information all the way on the other side of the diagram using a lot of joins. First, I turn the query into a sub-query...

code:

SELECT top_districts, category.name AS popular_genre, COUNT(*) AS genre_count
FROM (
  SELECT district, COUNT(*) as district_count
  FROM address
  GROUP BY district
  ORDER BY district_count DESC
  LIMIT 5
) AS top_districts

Then I add the joins:

code:

JOIN customer ON top_districts.district = address.district
JOIN rental ON customer.customer_id = rental.customer_id
JOIN inventory ON rental.rental_id = inventory.rental_id
JOIN film ON inventory.film_id = film.film_id
JOIN film_category ON film.film_id = film_category.film_id
JOIN category ON film_category.category_id = category.category_id
GROUP BY top_districts.district, category.name
ORDER BY top_districts.district, genre_count DESC;

But no good. I think maybe the problem is with my sub-query? It comes back with district and count kind of crammed into a single field and that may be causing the issue? I've been working with ChatGPT a little bit and it says this code is golden. I'm trying not to rely on AI so much though, for, well, reasons like this.

Specifically the error I'm getting back when I use this code altogether is:

ERROR: missing FROM-clause entry for table "address"
LINE 10: JOIN customer ON top_district = address.district