 I'm going to cheat a little bit. I'm actually more nervous for this talk than usual, because I am going to be doing a lot of live SQL. And SQL is not fun or exciting. But we'll see how this goes. So a really quick background. I head up Cytus Cloud. I work at Cytus Data. Before that, I was at Roku for five and a half years, working a lot with Terrence and Schneems. I curate Postgres Weekly. So if you like this talk at all, go sign up. It's a weekly newsletter about Postgres and trying to make SQL not so awful. A little bit of audience participation here. Does anyone here not use Postgres? A few hands. I'm sorry this may not be interesting at all to you. But by the end of it, you should realize why SQL's not so bad, why you should be using Postgres. I show this slide at pretty much every conference I talk about Postgres at. It's a great database. It's really powerful. It does a ton of things. If you're not sure what all these things are, JSON-B, which is a binary JSON directly in your database. There's transactional DDL. So if you're running a migration and something fails, you can actually roll it back. Listen, notify is PubSub directly in your database. You've basically got anything you could want. It's less of a relational database and more of a platform. This is a really fun email from the Postgres archives from about 10 years ago. If you're not familiar with Tom Lane, he's written a ton of code that you've leveraged. He used to, let's see, he co-authored the spec for JPEG, wrote the spec for PNG, authored libJPEG, libPNG. And then for the last 10 years or 20 years or so, he's been a major committer to Postgres. He's probably 50% of every Postgres release in a way. But in short, basically the naming of PostgresQL was the worst decision they ever made. This was 10 years ago and they still haven't changed it since. Postgres is a perfectly fine send and import. All right, so one more kind of fun one. This is from a colleague. I think this sums it up pretty well. It's more of a platform than anything else. So how many people like writing SQL? That's a few hands. How many people like reading other people's SQL? There's a reason for this because most of the time people write SQL and it comes out looking like this. It doesn't have to, we'll get to it here in just a second. But first, thanks to Kylie's talk, I had to go and interject this. So let me go ahead and execute that query. I don't know if you're gonna be able to see it at all. You can see the results. So directly from this query, I'll resize my screen a bit. We get Amanda Rot generated from that one query. I'm not gonna talk about how to write that query at all today, but I am gonna go ahead and now start to dig into a little bit of live SQL. So first of all, I'm not a, well, let's see. How many of you have a BashRC setup? Most people? Or VMRC, et cetera. How many of you have a PCQL RC setup? A few hands there. It's super handy. You can do all sorts of things in there. Trick out your PCQL, which is the editor that comes with Postgres. This is mine. There's a lot of things in here. One thing that's really nice is you can see my history file. I record it for every single database. So I could actually go and look at which databases I've connected to. So every database I've ever connected to on this machine and I can cat all of the queries I've ever run for my Keeper vWear database. So if you've ever written a query, came back two months later and completely forgotten what it was, it's there. I haven't lost it. Backslash timing is really nice to have on. This is gonna show how long it took for me to run every single query. Actually making nulls look like something that's distinguishable. So now I'm gonna hop in here to my database. So in this context, what I've got is a really simple application. Assume this is a database as a service where people come in and provision things and deprovision things. And the query we're gonna try to create here live, hopefully, is gonna show what is our daily run rate and how that's changing over time. So it's something pretty simple that a lot of businesses would care about. Basically, if nothing changes today on Monday, this is how many users have provisioned a database and this is how much I would bill on a monthly basis. And then how's that changed tomorrow based on what people provision and deprovision. I can do backslash d and describe my tables. So this is gonna show me the structure of them. I've got, we can actually just go ahead and query. This is gonna show me one record here. I've got the ID of this provision request, just a standard kind of ID of the billings. I've got a formation ID which references something else in my system. My organization, which is tied to my user. And then a couple of interesting fields here. So I've got this period, which is using a timestamp range. So Postgres has a range type, which has a value of from and to in that column. So it's actually a data type that is known as a range type. So I don't have to do a from and a to in two different fields. And I've got the price per month. I mentioned my pcqlrc. One thing that's really handy here is I have backslash auto on. If you notice it formatted my output like, like this. If I grab two of these, it's gonna do this right here. It's gonna be nice and kind of pretty on my screen. If I grab maybe just the period and my price per month, because it knows into my screen with is actually gonna format this for me. So it's gonna be pretty handy and just interacting with it and not having to do anything. All right, so let's see if we can actually do this here. The first thing I'm gonna do, we've got all of our billings right here. So we can see, I started billing on the first of the year. I'm first gonna go ahead and generate some time. I'm gonna go with the first of the year up to now. And I'm going to do this as a one day interval. Cool, so this is gonna give me kind of what I'm gonna project out into an Excel file that's gonna have my date for every single day of the year. I'm gonna go ahead and just make this a little shorter for now just for making it a little easier to iterate with. All right, so now actually let's start doing a little bit more here. First, I'm going to backslash E. Backslash E is gonna pop open my default editor. If you set your environment variable of your editor, this could be some blind text. This could be Emacs, this could be VI. So that's gonna be really handy for me to actually work in something that's not just a CLI right there. First, I'm going to set up my dates. And so this is a CTE. CTEs are extremely handy and are gonna be kind of key to making SQL actually look like something that people can follow. So right there, what this is gonna do is create this kind of, it's a common table expression, but create this temporary table view sort of during this query. From here, I can come back down here and say select star from dates. Gonna name this column. And we get the exact same thing. So nothing special there. But now what I can do is start to come in here and chain these together. First though, I'm gonna hop back over here and see. So on the billing side, I wanna look and see, okay, I've got period and price per month. These are the main things I'm gonna care about. Let's run this one and then hop back in here. So now I'm gonna start to try to grab my billings. So I'm gonna grab period and price per month. And that was from billings. Now I'm actually gonna try to do some logic here and say where period, lower period. So this is how I'm going to extract the basically the lower end of that date range. So what's the first value? Then what's the upper value? And I wanna say it's going to be greater than this date over here. So that means I need to come back here and put date in my other one. And I'm going to make sure it's not been deprovisioned. So if I scroll back up here, you can see that an example one was provisioned on January 1st. It was deprovisioned on May 3rd. So I wanna make sure that the upper bound is null or upper is greater than the current date. So basically is it gonna fall inside this date range? And so what I'm actually doing here because there's no join on the DT, I'm actually doing a cross join here. So it's basically gonna say for every single record, give me a record that then matches to that date. And then let's actually do the evaluation there. So from here, now I can say, okay, we can do this and now I'll get the correct table. But we're still getting that other result. So what I need to do now here down here now is do this from Billings roll up. Cool, so you can see now that I've got, let's go ahead and project out the date as well. So we can actually see what's going on. One thing for SQL to actually make it readable, heavily indent and align things. Some people love to actually put commas at the beginning of a line, so it's easier to delete. I hate it for looks, but it does make it a little more readable. But if you'll notice, this is starting to follow a kind of code structure that's much more similar to how we write code than SQL typically. So now you can see on, I've got this date here and I can see that this one fell in this period. I've got this one, that's not right. Cool, so let's go back in here and see. So my lower should be less than there. Cool, and now that actually looks pretty good. So now we've got on the second of the month, we can see that we had one database provision then, right? So it's in between this range on the third, same thing. And we can see down here on the fourth, we've started to get two. So that makes pretty good sense. Let's see if we can now just sum this up and see if we can get a daily rate. And here, pretty basic just sum of price per month. Group that by a handy thing that you should never do. I can group by DT or I can group by one, which is actually just gonna be the column number there. If you've got like really gnarly kind of expressions, this can be a shortcut. Never put it something into production this way because then people start moving columns and you get different results. But it's handy in the short form. All right, let's see if we've got a daily summary now. Perfect, thank you. And again, if I exchange out the right table. Cool, so this is actually kind of starting to look almost like a little bit of a thing I'd put into a dashboard. Now what I wanna try to do, if it works, is a window function. So what I wanna do is it's great to have my data like what am I billing each day? But now I wanna say how am I growing? Am I growing month over month, week over week, that kind of thing? It would be easy to pull this out into Ruby and calculate that iterate over all of it. It would be easy to do this in Excel, but then I've gotta extract the data on a daily basis. Why not just do it in SQL because we can't? So now I'm gonna come in here and I'll just start with this one actually. So I'm gonna grab the DT. I'm gonna name this as my total for the day. And then I'm gonna come down here and say group, or no, window w as order by date. And so what this is gonna do, a window function gives you the ability to iterate over the results that you have. So you can do recursive things here. If you're kind of curious of an example, here's a nice little example that'll say, let me rank all of my rows and then group them by department. And this is just gonna project out of, oh, this person ranked by salary, ordered by department, and I can know where they rank. So this is really nice to say, oh, I wanna find the top paid person or other things. In our example, what we wanna do is actually interact with some of the previous rows. So if we wanna compare January 7th to January 1st. So I'm gonna create the window right here and then I'm gonna come back in here and do this function. Lag is a built-in function for window functions. Tag total one over w. Sweet. So now what this is doing is saying, okay, here's my total today, here's my total yesterday. We can easily add another one of these and say for a week ago and just go back seven days. Now we don't have enough data, so let's go back up and change our series a little bit. And now we can see from a week ago. Now I can actually come back in here and now I have everything to need. I need to do month over month growth, week over week growth, anything like that. I'm bad at remembering actually how to do math, so I'm gonna cheat and copy and paste that. We can do it right here live. It's a little annoying here with Postgres that we have to, so here I would do my lag from let's say seven days ago divided by the same thing. We have to do a lot of casting here which is kind of annoying because it likes to divide integers by integers and it doesn't quite know how to kind of round those automatically. Should work maybe. So I've got to put my window function back in right here and there in SQL we created a, what often it takes a lot of ETL, we pull up into Ruby, we do all sorts of things. But I've got my run rate on a daily basis right here. I've got my yesterday total week ago total and we've got that this is a 66% growth from a week ago. It looks like I'm doing math wrong there but you get the idea if you get the formula right there. So hopefully that wasn't too painful to watch live coding but if we come back through one more time to just kind of look at the query, hopefully what you've got here at the end of this is something that anyone could come in and read very, very clearly. I think Kylie's talk was very interesting that when there's a bug in code, like go back, clean it up, fix it and make it more readable. We should be doing the same things to SQL. So I think I saw two hands of people that liked to read other people's SQL but proper indentation, building blocks like this, you can actually follow everything that's kind of going on throughout it instead of five joins at once. And at the end you can do things that are fairly complex that you wouldn't be able to do otherwise because you're busy reasoning about the data. So a few tips, writing better SQL. Set up your P-SQL RC, it's really handy. Use a defined style. I make sure all of my kind of columns that I'm querying line up. Commentable expressions, with clauses, whatever you want to call them, they're huge for actually making things readable. The hardcore Postgres experts will say they're an optimization boundary and they are. But on a report like that, it runs in, how long did that run in? 1.4 milliseconds, even with a ton of data. If that takes a couple of seconds and I'm running it once a week, I don't care too much. And read Postgres weekly for more tips and kind of things like that. That's it.