I will come to the solution video for exercises 4.3. So first question, how many tests do we have running or have we run in this dataset that we have come to class? We are going to use the events table dsv.events. We want to look specifically at the case where the event name is test_assignment, and we want to look specifically at the case where the parameter name is test_id. Because what I'm trying to do here is, count how many distinct test IDs we have, but first I have to get that out of the events table. So here, didn't hit the limit button. There we go. All right. So here we are getting the test_id under this column parameter value. So really just what I want is, to do the count. Distinct parameter_value as tests. We got four running. Another way we could do this is, we could just say, distinct parameter_value, and I would call this as test_id. You can see that we have a test number four, test number five, test number seven, and test number six all running. Okay. Now, let's move on to the next question. So I want to check and see if there are potential problems with test assignments to see if there are any days missing or anything like that. I'm just going to cut and paste some of this over because we can use a little bit of it. One of the things that I want to do is, pull in a date column. So let's just run this with a star, and I'm going get rid of this parameter restriction. I want to look at the date of the event time as the day. Okay. Then I just want to count how many rows there are. I'm not trying to get too fancy here. For me to do this count, I need to also going to group by. Okay, so I'm going to name this event. Okay. So now I've got a count of how many events happen each day. I want to add a chart, and I want to just be able to look at this. Day, event rows. Okay. So this seems a little bit weird. We've got a lot of events happening at many different times, it's unclear to me what's going on at this line in-between. So the thing that I want to be see, that I can't see here is maybe these are different tests happening. So now I want to uncomment this out, and then I want to look at the parameter_value. That's the test_id. Test_id, right? Because you were stripped it just soon as parameter name. Then I'm going to have to add that into the group by as well without a comma. Okay. So now we can see if I'll test. Let's see. I can add that into the color field. Okay. So here you can see for test five, it was running in this time period. For test six, it was running in this time period, and for tests seven, it was running over here. Okay. So that makes me feel a little bit better, doesn't seem like there is, for any of these tests, any big problems so far in having assignments we are looking. Okay. I'm sure there's a lot more that you could do, but that's just a first check. Okay. We're going use this in the next part of the assignment, so I cut and paste it. So here our goal is to make sure users are assigned to only one treatment group, and we're checking for potential assignment problems just in tests five. So we're going to use this base table that we just created. I'm going to wrap it in some parentheses, and call it test_assignment. Maybe test_events. Okay. and I'm going to select from it. What I want to select is, I want to select the user_id, and then I want to know how many different tests assignments they had. So I'm going to use the test assignment column. I want to count distinct tests assignments. Okay. If I do that, I'm going to need to put it in the group by at the bottom, and I also think that I want to know about the cases where someone is assigned to multiple assignments. So I want to order by the count of assignment descending. Let's see what we get. Okay. So we're seeing some users who were assigned two different distinct assignments. That's troubling. Part of the reason that's happening maybe it's because I forgot to add in the "WHERE" clause. I specifically want to look at the case where test_id is equal to five. Okay. Now, we have a whole bunch of users, we'll have exactly one assignment in this test number five. Another way we could have done this is, we could have added test_id in to ref the selecting a group by, and now we'll check it for all of the tests. So if you get rid of this "WHERE" clause and run this query where I'm looking at, every test ID, every user ID, how many distinct assignments do they get in that test? I'm sorting descending. So now when I look at this and I see that the biggest number I can get here for this count distinct tests assignments is one, then I know that all of my tests are actually fine. Okay. So now let's move on to the next problem, and we're going to use exercise 1.3, which we already did as a template. We're going to use that to write a query that returns the table of assignments. So this is just basically turning an event stream into a table, just like we've done before, with the assignment and dates for each test. So I want to just remind you what this event looks like. I've selected star from the events table with the test assignment, event name, and I've ordered it by event IDs so that you can see that there's two parameters we care about. The test_id, and the test_assignment. So this says that our user 147689 was assigned to test five, the assignment of one, which I'm going to assume is treatment at this time. Okay. So we need to use two parameters, and that's great because in this exercise, step two, we've got two parameters here. That's for the view item event. We have item_id and referrer. So we're going to just cheat off of this. We're going to cheat off of this template. Instead of looking at events that are view item, we're going to do a test_assignment. Instead of looking at the item_id, we're going to do the test_id, and we're going to call it test_id. Then, instead of looking at referrer, we're going to look at the test_assignment, and we're going to call that test_assignment. So let's see if it works. Great. So now we can see that this user 147689 was assigned to test five in test group one.
