"select e.firstname as firstname,
m.salary as manager_salary
from employees e join employees m on e.manager_id = m.id
where e.salary > m.salary;
`"
Ravi K. - "select e.firstname as firstname,
m.salary as manager_salary
from employees e join employees m on e.manager_id = m.id
where e.salary > m.salary;
`"See full answer
"SELECT customer_id,
order_date,
orderid AS secondearliestorderid
FROM (
SELECT order_id,
customer_id,
order_date,
ROWNUMBER() OVER (PARTITION BY customerid, orderdate ORDER BY orderid ASC) AS rank
FROM orders
)
WHERE rank = 2
ORDER BY orderdate, customerid
`"
Tiffany A. - "SELECT customer_id,
order_date,
orderid AS secondearliestorderid
FROM (
SELECT order_id,
customer_id,
order_date,
ROWNUMBER() OVER (PARTITION BY customerid, orderdate ORDER BY orderid ASC) AS rank
FROM orders
)
WHERE rank = 2
ORDER BY orderdate, customerid
`"See full answer
"WITH filtered_posts AS (
SELECT
p.user_id,
p.issuccessfulpost
FROM
post p
WHERE
p.postdate >= '2023-11-01' AND p.postdate < '2023-12-01'
),
post_summary AS (
SELECT
pu.user_type,
COUNT(*) AS post_attempt,
SUM(CASE WHEN fp.issuccessfulpost = 1 THEN 1 ELSE 0 END) AS post_success
FROM
filtered_posts fp
JOIN
postuser pu ON fp.userid = pu.user_id
GROUP BY
pu.user_type
)
SELECT
user_type,
post_success,
post_attempt,
CAST(postsuccess AS FLOAT) / postattempt AS postsuccessrate
FROM
po"
David I. - "WITH filtered_posts AS (
SELECT
p.user_id,
p.issuccessfulpost
FROM
post p
WHERE
p.postdate >= '2023-11-01' AND p.postdate < '2023-12-01'
),
post_summary AS (
SELECT
pu.user_type,
COUNT(*) AS post_attempt,
SUM(CASE WHEN fp.issuccessfulpost = 1 THEN 1 ELSE 0 END) AS post_success
FROM
filtered_posts fp
JOIN
postuser pu ON fp.userid = pu.user_id
GROUP BY
pu.user_type
)
SELECT
user_type,
post_success,
post_attempt,
CAST(postsuccess AS FLOAT) / postattempt AS postsuccessrate
FROM
po"See full answer
Data Engineer
SQL
+3 more
🧠 Want an expert answer to a question? Saving questions lets us know what content to make next.
"-- Write your query here
select
id,
(case
when p_id is null then 'Root'
when pid in (select id from treenode_table)
and id in (select pid from treenode_table) then 'Inner'
else 'Leaf' end) as node_types
from treenodetable
order by 1;
`"
Anonymous Roadrunner - "-- Write your query here
select
id,
(case
when p_id is null then 'Root'
when pid in (select id from treenode_table)
and id in (select pid from treenode_table) then 'Inner'
else 'Leaf' end) as node_types
from treenodetable
order by 1;
`"See full answer
"Here's a simpler solution:
select
u.username
, count(p.postid) as countposts
from posts as p
join users as u
on p.userid = u.userid
where p.likes >= 100
group by 1
order by 2 desc, 1 asc
limit 3
`"
Bradley E. - "Here's a simpler solution:
select
u.username
, count(p.postid) as countposts
from posts as p
join users as u
on p.userid = u.userid
where p.likes >= 100
group by 1
order by 2 desc, 1 asc
limit 3
`"See full answer
"select employeename, employeeid, salary, department, DR
from (
select employeename, employeeid, salary, dense_rank() over (partition by department order by salary desc) DR, department from employee
)
where DR <=3
order by department, DR"
Sreeram reddy B. - "select employeename, employeeid, salary, department, DR
from (
select employeename, employeeid, salary, dense_rank() over (partition by department order by salary desc) DR, department from employee
)
where DR <=3
order by department, DR"See full answer
"WITH ActiveUsersYesterday AS (
SELECT DISTINCT user_id
FROM user_activity
WHERE activity_date = CAST(GETDATE() - 1 AS DATE)
),
VideoCallUsersYesterday AS (
SELECT DISTINCT user_id
FROM video_calls
WHERE call_date = CAST(GETDATE() - 1 AS DATE)
)
SELECT
(CAST(COUNT(DISTINCT v.userid) AS FLOAT) / NULLIF(COUNT(DISTINCT a.userid), 0)) * 100 AS percentagevideocall_users
FROM
ActiveUsersYesterday a
LEFT JOIN
VideoCallUsersYesterday v ON a.userid = v.userid;"
Bala G. - "WITH ActiveUsersYesterday AS (
SELECT DISTINCT user_id
FROM user_activity
WHERE activity_date = CAST(GETDATE() - 1 AS DATE)
),
VideoCallUsersYesterday AS (
SELECT DISTINCT user_id
FROM video_calls
WHERE call_date = CAST(GETDATE() - 1 AS DATE)
)
SELECT
(CAST(COUNT(DISTINCT v.userid) AS FLOAT) / NULLIF(COUNT(DISTINCT a.userid), 0)) * 100 AS percentagevideocall_users
FROM
ActiveUsersYesterday a
LEFT JOIN
VideoCallUsersYesterday v ON a.userid = v.userid;"See full answer
"SELECT
s.Sale_Date,
SUM(si.Quantity * si.SalePrice) AS TotalRevenue
FROM Sales s
JOIN SaleItems si ON s.SaleID = si.Sale_ID
GROUP BY s.Sale_Date
ORDER BY s.Sale_Date;
"
Bala G. - "SELECT
s.Sale_Date,
SUM(si.Quantity * si.SalePrice) AS TotalRevenue
FROM Sales s
JOIN SaleItems si ON s.SaleID = si.Sale_ID
GROUP BY s.Sale_Date
ORDER BY s.Sale_Date;
"See full answer
"SELECT d.name as departmentname,e.id as employeeid,e.firstname,e.lastname,MAX(e.salary) as salary
FROM employees e LEFT JOIN departments d
ON e.department_id=d.id
GROUP BY department_name
ORDER BY department_name;"
Anisha S. - "SELECT d.name as departmentname,e.id as employeeid,e.firstname,e.lastname,MAX(e.salary) as salary
FROM employees e LEFT JOIN departments d
ON e.department_id=d.id
GROUP BY department_name
ORDER BY department_name;"See full answer
"SELECT
u.user_id,
u.user_name,
u.email,
ROUND(AVG(CASE WHEN b.status = 'Unmatched' THEN 1.0 ELSE 0 END), 2) AS avgunmatchedbookings
FROM
users u
LEFT JOIN
bookings b ON u.userid = b.userid
GROUP BY
u.user_id,
u.user_name,
u.email;
`"
Akshay D. - "SELECT
u.user_id,
u.user_name,
u.email,
ROUND(AVG(CASE WHEN b.status = 'Unmatched' THEN 1.0 ELSE 0 END), 2) AS avgunmatchedbookings
FROM
users u
LEFT JOIN
bookings b ON u.userid = b.userid
GROUP BY
u.user_id,
u.user_name,
u.email;
`"See full answer
"The user table no longer exists as expected - I get an error that user does not contain user_id.
Note that querying the table results in only user:swuoevkivrjfta
select * FROM user
`"
Evan R. - "The user table no longer exists as expected - I get an error that user does not contain user_id.
Note that querying the table results in only user:swuoevkivrjfta
select * FROM user
`"See full answer
"How do you find consecutive days for login (MySQL, SQL, date, subquery, MySQL 5.7, development)?
1
Follow
Request
Answer
More
All related (34)
Recommended
📷
Trausti Thor Johannsson
·
Follow
Been using MySQL for more than 16 yearsDec 27
There are functions like DATEDIFF but there are also BETWE"
Hayatu H. - "How do you find consecutive days for login (MySQL, SQL, date, subquery, MySQL 5.7, development)?
1
Follow
Request
Answer
More
All related (34)
Recommended
📷
Trausti Thor Johannsson
·
Follow
Been using MySQL for more than 16 yearsDec 27
There are functions like DATEDIFF but there are also BETWE"See full answer
"--country names are UPPERCASE but the table in the in the question showing lowercase. That's why it took me a while to figure it out until I ran the country column
WITH RECURSIVE Hierarchy AS (
SELECT
e.Emp_ID,
CONCAT(e.FirstName, ' ', e.MiddleName, ' ', e.LastName) AS FullName,
e.Manager_ID,
0 AS Level,
CASE
WHEN e.Country = 'IRELAND' THEN s.Salary * 1.09
WHEN e.Country = 'INDIA' THEN s.Salary * 0.012
ELSE s.Salary
"
Victor N. - "--country names are UPPERCASE but the table in the in the question showing lowercase. That's why it took me a while to figure it out until I ran the country column
WITH RECURSIVE Hierarchy AS (
SELECT
e.Emp_ID,
CONCAT(e.FirstName, ' ', e.MiddleName, ' ', e.LastName) AS FullName,
e.Manager_ID,
0 AS Level,
CASE
WHEN e.Country = 'IRELAND' THEN s.Salary * 1.09
WHEN e.Country = 'INDIA' THEN s.Salary * 0.012
ELSE s.Salary
"See full answer
"Required output in the solution not the one requested from the question. only customerid, firstname, last_name and years were required. Please this needs to be very clear.
Otherwise my answer is
with totalorderyear as (
SELECT
o.customer_id,
c.first_name,
c.last_name,
EXTRACT(YEAR FROM o.orderdate) AS orderyear,
COUNT(o.orderid) AS totalorders
FROM orders o
LEFT JOIN customers c
ON c.customerid = o.customerid
GROUP BY o.customerid, c.firstname, c.last"
Gloriose H. - "Required output in the solution not the one requested from the question. only customerid, firstname, last_name and years were required. Please this needs to be very clear.
Otherwise my answer is
with totalorderyear as (
SELECT
o.customer_id,
c.first_name,
c.last_name,
EXTRACT(YEAR FROM o.orderdate) AS orderyear,
COUNT(o.orderid) AS totalorders
FROM orders o
LEFT JOIN customers c
ON c.customerid = o.customerid
GROUP BY o.customerid, c.firstname, c.last"See full answer
"select sum(orderquantity) as totalunitsorderedyesterday
from orders as ord join items as it
on ord.itemid=it.itemid
where order_date="2023-10-14""
Rudra pratap S. - "select sum(orderquantity) as totalunitsorderedyesterday
from orders as ord join items as it
on ord.itemid=it.itemid
where order_date="2023-10-14""See full answer
Asked at 
Asked at 
Asked at 
