From the table below I need to write an SQL query, such that each user that has ever created a role will return 4 values:
User name in the UserName column;
No of created roles in NoOfCreated column;
No of roles enabled in NoOFEnabledRoles column
No of updated roles in theNoO
fUpdatedRole column.
Sorting: by username in descending order. also username should return no leading or trailing white spaces and should be in uppercase.
Query shouldn't return any null value for numerical columns, null should be replaced by -1(incase no role are updated by user,or no roles are created by a given user that are enabled).
Note: values in created at updated at are not consistent
CREATE TABLE UserRole(
Id bigint NOT NULL AUTO_INCREMENT,
Name varchar(100) NOT NULL,
Description varchar(200) NULL,
IsEnabled bit NOT NULL, -- 1 if a role is enabled, 0 otherwise
Created date NOT NULL, -- When a role was created
CreatedBy varchar(200) NOT NULL, -- Who created a role
Updated date NULL, - When a role was updated (if at all)
UpdatedBy varchar(200) NULL, - Who updated a role (if at all)
CONSTRAINT PK_UserRole PRIMARY KEY ( Id ASC )
)
Can anyone please help me with the query thanks in advance!
The reason @yosiasz asked for what you've tried is because this looks like either school work or an interview problem.
I also hate these types of problems, not because of their nature but because of the ambiguous requirements the testers write and the "hint" they expect you to correctly understand in order to get it right. The really frightening thing is that people actually do write requirements like this.
And no... this isn't meant to be easy for someone that hasn't done such a thing before.
I'm not going to write the code for you but here's my interpretation of the problem...
Every user in a system will show up 1 or more times in this table meaning that the complete list of all users can be from a unique list of user names from the "Name" column. Some will have created or modified rows for others (or maybe themselves as well) in this table and some will not have.
So the bottom line is that you need to create the unique list of users and then use that in a LEFT JOIN to create the requested totals.
Give it a shot on your own. You're the one that has to be able to discuss the code you write in an interview and so it's also good practice even if you're still in school.
Also be advised that the "AUTO_INCREMENT" keyword does not exist in SQL Server T-SQL. That means that the person who wrote this question is expecting the answer in a different dialect of SQL and things like IIF() may either be different or not exist at all. What I'm saying is that no matter where you might get an answer from, it could be perfect yet still incorrect because of the dialect for different database engines.
I could definitely be wrong about the nature of the post. I guess the nature of the post doesn't actually matter at all. I absolutely do agree that it's good if the OP at least tries first and then identifies what they're having problems with.
tried this but not working.
Select trim(upper(CreatedBy)) as UserName,
count(CreatedBy) NoOfCreatedRoles,
SUM(IsEnabledAndCreated) as NoOfCreatedAndEnabledRoles,
sum(IsEnabledAndUpdates)
from
(
SELECT CreatedBy,
Case
WHEN isEnabled = 1 THEN 1
ELSE -1
END as IsEnabledAndCreated,
Case
WHEN updated is null THEN -1
else 1
END as IsEnabledAndUpdates,
UpdatedBy
FROM UserRole
) t
WHEN any_value(isEnabled) = 1 && count(CreatedBy) >0 THEN count(any_value(isEnabled))
ELSE -1
END as NoOfCreatedAndEnabledRoles,
Case
WHEN (select count() as total from UserRole where UpdatedBy = t.CreatedBy ) > 0 Then (select count() as total from UserRole where UpdatedBy = t.CreatedBy )