Hey, welcome to the solution video for exercise 4.4. Let's start with the first exercise. So here, we're going to reuse a table from exercise 4.3. We're going to compute a metric that just measures just whether a user created an order after their test assignment or not. Remember some requirements for this is, if the user has zero orders, they still need to have a row that shows up in the table with a zero that shows that they had zero orders. If the user's not an experiment, they should not be included. So here's the table that I've already kind of pulled up here. The event id, the event time, the user id, the test, and the test assignment that they were given. I want to use this, I'm going to call this test events. I'm going to use that as kind of my backbone for this query. Okay, and from it I want to do test events, I want to do the test study, and the test assignment, and the user id and let me because I'm going to do a join on this, let me put test events in front of there. I'm having trouble with the cursor. To that what I want to do a left join with this dsv orders table, on order's user id is test events. Because I want to join in this stuff, user id to figure out whether a user actually made an order or not. I might think about putting the event time and the created_at for the order time in here, but I'm actually going to put it up in the top up here. So I'm going to say, case when the orders.created_at is after the test_event.event_ time. Then, I want to count the order id, I think it's called invoice id. Yeah. I want to count the invoice id, else null end. So if they didn't order anything after their test assignment, then I don't want to include anything. This is orders_after_assignment. Let's see what that looks like. I spelled table name wrong. So let's try that again, and this is called test_events, I spell the table name on here. So here we have a bunch of users. Some of them have no orders after assignment and then we can see this one user, and because there's three line items in the orders table, we're getting three copies of this orders after assignment. So if we wanted to count how many orders there were, we would probably want to do a count distinct on the invoice id. But right now here actually we just want to figure out if there was an order. So here, I'm just going to say, I'm going to change this to be a max statement of the case when order created_at is after event time, and then instead of invoice, I'm just going to say one, and otherwise I'm going to say zero. Then, I'm going put it in, so all the parentheses I need, one of them binary and in order to do this max statement, I need to put all of this stuff in the Group By down below. So here, we can see now we have this column, where sometimes it's a one, sometimes it's a zero. But if I'm looking at it based on user id, I'm not going to find people showing up here multiple times for the same test. So let's borrow this query because we're going to need it for the next one. Here, we're doing something similar except instead of just the binary, we're going to compute the total number of invoices, the total number of line items, and the total revenue from these orders. So I'm going to changes to just to start with the orders. So we're going to think about switching this back to invoice id for each order. Otherwise, we're going to do null and I'm going to do a count distinct. So that gets me. See it? It looks fairly similar. But okay, here, I think we found someone in here with two orders. So here, this user has two orders after their test assignment. So here, it differs from the order binary. We can change this to also be line items. So if we wanted some instead count how many items they purchased, we could do that. So here, you can see this user ordered one order and had two line items. This person had three line items. Then if I want to count total revenue, I instead want to sum something. So it's going to be little distinct, but it's going to be the price. So let's just make sure I have that. Yeah price, [inaudible] and then just make this a little prettier, so it's all on that line. So here we go. We can see again this user ordered, made one order with two items. The total revenue for those items was 89. So this is a way that you can generate some of those metrics if you're going to measure the results.
