5 kyu
SQL Basics: Simple VIEW
4,034 of 4,036matt c
Loading description...
SQL
Fundamentals
This comment has been reported as {{ abuseKindText }}.
Show
This comment has been hidden. You can view it now .
This comment can not be viewed.
- |
- Reply
- Edit
- View Solution
- Expand 1 Reply Expand {{ comments?.length }} replies
- Collapse
- Spoiler
- Remove
- Remove comment & replies
- Report
{{ fetchSolutionsError }}
-
-
Your rendered github-flavored markdown will appear here.
-
Label this discussion...
-
No Label
Keep the comment unlabeled if none of the below applies.
-
Issue
Use the issue label when reporting problems with the kata.
Be sure to explain the problem clearly and include the steps to reproduce. -
Suggestion
Use the suggestion label if you have feedback on how this kata can be improved.
-
Question
Use the question label if you have questions and/or need help solving the kata.
Don't forget to mention the language you're using, and mark as having spoiler if you include your solution.
-
No Label
- Cancel
Commenting is not allowed on this discussion
You cannot view this solution
There is no solution to show
Please sign in or sign up to leave a comment.
The details isn't easy to get, but the kata is fine. Without comments I couldn't manage it I believe.
I've been doing SQL for about 1 week now and this one took me a good few hours. I had to go to bed with it unsolved, but just finished first thing in the morning. Took me a while to realise what I was actually supposed to be doing, and it really annoyed me whilst nothing seemed to be going right, but I rewrote the whole query from scratch and got it done. Good learning experience, didn't even know views existed before and think they're a really useful thing to know about.
Terrible description of this kata! In addition to what other comments said, you need to order by id in select from created view.
Issue number 1: The result will be shown even if you create only comment with "create view" and correct name of the view. So simply you will pass if you just write a correct select statement with additional comments which is not what is expected right?
Issue number 2: If you create VIEW after that you need to write in edition "Select * from that_view" otherwise there will be no data selected (no rows selected). I was expecting that select statement will be generated automatically.
I think the attack is on SIMPLE. Might be simple view, but not a simple query.
This comment has been hidden.
I don't think so. the group by conditions are different - there is no reusable logic here
The prices should have rounding that is mandatory. I used sqlite and could not get it to work, while it worked without issues under postgres
Probably not a 5kyu, 6 or 7
Ranks can't be changed
The explanation is confusing
English used in question is pathetic
Is there other way of filter rows based on information that needs to be generated throught an aggregate function which depends on declaring a certain column in the SELECT, without the need to declare said column because it shouldn´t be shown?
I used -- to comment out create view statements, submitted the code, and it actually passed through the tests. It might be worth to check if the statements are commented out or not?
This comment has been hidden.
remember, you are creating a view, not just a select statement. That helped me along on this.
This comment has been hidden.
Honestly I didn't like how this question was worded. It made it very difficult for me to understand everything I needed to do.
i guess the question was not explanatory
This comment has been hidden.
Never mind, it's just a matter to remove the "CREATE VIEW members_approved_for_voucher AS" and keep only the select.
A lot of the product names have the same product_id. I'm new to this but that doesn't make sense. Can someone explain this?
Remember that you need to put ; after a view creation statement, this way it will work here. Also SQLite isn't warning you about an errors of fields you put in HAVING, so beware.
Needs a secondary test for comparing departments. I was able to hard code departments.id <= 3 to limit which departments are used. Knowing the current state of the data, should not allow me to hard code the answer. I assume there is a way to have a different dataset when doing final testing?
This is the most confusing challenge I have ever seen. The wording makes no sense. To sum up the previous comments below for everyone who comes after.
Oh snap, I read it totally differently, now I see the point.
Thanks for clarifying (1). Was stuck for a while on this.
Thanks for the nice summary! It helped me a lot! I might also add to your summary,
Cheers!
Like the others thank you for clarifying point 1, that one would have had me confused for a while
Your first point helped me out!
I also want to add to make sure to ORDER BY id ASC
+1 helpful rewording! Kudos to @matt c for a highly subscribed and helpful kata.
Oh!!! So that's why my solution isn't working. I thought it was meant to include only spendings in a department that had over 10,000 in sales.
Thanks :D
The test randomly fails and succedes for the same SQL query :( The testing framework is not robust or cannot handle temporal views.
You need order the results ascending and then it works because the expected results are ordered as well. It shouldn't be like this cuz there is nothing about ordering the results in the instructions
actually there is: "... ordered by the members id" - check description
FOR EVERONE WHO STRUGLE!
I would say it's better to do the actual task and create a view instead of trying to get around the proper task. It's stupid to exercise and not do it properly.
Could someone explain this sentence:
"members who have spent over $1000 in departments that have brought in more than $10000 total ordered"
do you want member spend over $1000? and also spend over $10000?
This comment has been hidden.
As others have mentioned, the VIEW part of this kata is not tested, beyond mere inclusion of the keywords. But in my opinion, the VIEW part is not nearly as important as the challenge of organizing the query itself. This kata uses a combination and expansion of several techniques found in the 6 kyu katas. I think it's a good test of whether your fundamentals are solid.
You can pass this with the create view part of the code commented out
Could you add a comma?: -- members who have spent over $1000 in departments that have brought in more than $10000 total, ordered by the members id
This comment has been hidden.
This comment has been hidden.
I make the Select query is already okay, but when add CREATE VIEW, no row is return, do you have any idea ?
This comment has been hidden.
Make sure to do a
SELECTfrom theVIEWitself.This comment has been hidden.
This comment has been hidden.
Great kata!
Thanks!
it's says "departments that have brought in more than $10000" but it should say "departments that have brought in more than $1000000"
grammar issue:
"This VIEW is used by a sales store to give out vouches to members who have spent over $1000 in departments that HAVE brought in more than $10000 total, ordered by the members id. "
done
Does anyone have the image for this problem? This is not the only Kata I have looked at that has an image that is broken when I load the page. I have tried on internet explorer and firefox.
image works fine for me
I cant see the image either. I've worked out 2 of the tables but I'm assuming there is a third table which is the prices? Can someone provide that info please?
http://i.imgur.com/hkEkGg1.png
I had the same problem!
This comment has been hidden.
"This VIEW is used by a sales store to give out vouches to members who have spent over $1000 in departments that have brought in more than $10000 total, ordered by the members id." So not all members with > 1000 should be included. Only the members that bought over $1000 worth of products in the selected departments (sold over $10000 worth of products).
This comment has been hidden.
You mean why it hasn't got a lower rating, don't you? I guess it's because one needs to cope with the broadly advertised "inappropriate wording" of the kata... ;)
GOOD kata ! You need to be careful not to get confused
Hi guys,
I am new to codewars, but i really do enjoy learning SQL with it. I spend over 30 min on this case and I still cant complete it. Well to be precise I have created the select statement perfectly to show exactly the same results as expected, but when I add create view view_name as statement it suddenly doesnt work. Any clues on what am I doing wrong ?
This comment has been hidden.
This comment has been hidden.
That's not an issue, that's a question.
The technical challenge of this kata is good and fun, but 80% of my time was spent trying to figure out what that challenge actually is. Do I need member revenue of > $1000:
(for anyone looking for clarification, it's #2)
I think using more specific language and adding an example scenario would increase the clarity, and thus overall satisfaction rating, of this kata.
This comment has been hidden.
any comments on my code ?
I managed to get the good results but the attempts fails because of the lack of order precision in the instructions paragraph
The "total_spending" column name is kind of misleading. You have to return total spending "in departments that has brought in more than $10000" to pass the kata.
I enjoyed this one. The select query was more interesting than creating the view - it was cool to see how different people used different ways to get the result on that (CTE, subqueries, other methods).
This comment has been hidden.
link still seems to work?
Works for me, I think it is your proxy :(
Sorry, it was a false alarm. I agree with that It could be based on my proxy.
This comment has been hidden.
i've seen this issue on multiple tests with the correct results.
This was an issue with SQL katas that's been fixed a couple of hours ago. Try again.
As others said the wording on this is somewhat difficult to parse. Instead of "This VIEW is used by a sales store to give out vouches to members who have spent over $1000 in departments that has brought in more than $10000 total ordered by the members id. "
What about: "This VIEW is used to give out vouches to members who spent over $1,000 in departments that have sales of over $10,000. Results should be ordered by member id ascending."
I've created the view and the select finally. As a result the actual rows absolutely the same as theexpected row. Even 12 passed and 1 failed. The text for the failure the next: "should should return the expected results" "expected true to respond to
true?or perhaps you meantbe trueorbe_truthy". After all I've tried choosing one from the best solutions and I've got the same bad result.Is creating a second view allowed? I wanted to but seems I am having a problem reaching the server, maybe because of that...?
(Tests sometimes seem to pass perfectly.)
sorry, just been accepted. thanks for reading!
the server was experiencing some issues since the migration but it seems to be working now :)
cool, thanks for the response!
I used a second view to crack up the query into comprehensible chunks. While tests pass, seems I am having a hard time submitting this solution.
Your Table Diagram image is being blocked by my network. I know, it's my problem (you might be saying "Well, get out of that network, noob!"). But is there a creative self contained solution in Markdown that might display Table Diagrams and Relationships without requiring embedding external images? I know it's my issue, but what do you think?
The wording of the challenge is confusing for me - maybe because I'm not a native English speaker. I understood we're looking for members having spent more than $1000 in total in departments with a turnover of $10.000 or more. But you're looking for such members having spent more than $1000 in at least ONE department with a turnover of at least $10.000.
Futhermore, there is a typo, "vourches".
also "vouches"
I agree. Wording in this kata is an issue
The phrase "ordered by the members id" is in a completely wrong place. The suite requires the whole result be ordered thus.
that's why I put it at the end, the ordering is specified so as far as I see it's sufficient. This is also a suggestion not a kata breaking issue.
it seems to me that it is not possible to create "with" queries or other views beside the one requested in the instructions. is that correct?
yes.
This comment has been hidden.
Then I'm guessing your view has errors or something.
If you're having problems just post your code below and mark it as a spoiler.
it hasn't. the query works perfectly without "create view ...". if I remove the "create view ..." line, the resultset is the expected. it's just that you somehow forced to make the whole query in one, even if I find it more readable and intuitive to have 2 queries, one for departments and one for people.
This comment has been hidden.
you're missing a semi-colon to sperate the statements...
good now. I took off the semi-colon after I've seen that "with < some_name > as ( < some_query > )" are not allowed. You should write in the description that further subqueries are not allowed.
but it is possible to have 'with' queries and subqueries.
the 15 solutions provided so far (yours included) beg to differ. I just tried again and: no, you can have only the "create view" and the query calling it right after. this is related to why I forgot the semi-colon, which I did use before.
uhm... the top voted solution has a
withstatement...not outside the create view
that's because of how codewars seperates it's sql statements.
mmalkavian post your solution (using WITH) that you expect to work. I can test and see if Codewars can be updated to handle it. The need to separate SQL statements may be causing some edge case issues.
@jhoffner: my comments referred to the fact that it was not possible to define the "with" subquery outside the "create view". I don't know if that has been changed now. I solved this issue by using some nested queries; personally, I find them ugly to read but that was it.
mmalkavian, if you still have your old solution could you post it below and mark it as a spoiler.
thanks.
The tests are not checking the "> 10000" brought in by the department condition.
I had to include that test to get the correct results and pass the test. Maybe it's resolved.
But I have another issue. I find the description a bit confusing. For me, the part "in a given department" sounds like 'spent > $1000 in a SINGLE department' not like 'a total of $1000 in one or more departments', but that doesn't match the expected results.
the tests do check for the 10000 and I've updated the description to mean 'spend > $1000 in all departments that have total sales above $10000'
I've modified the dataset to better populate the data with the occurance of the '> 10000' rule to be more frequent invalidating some solutions that did not provide that check.