1581.
Customer Who Visited but Did Not Make Any Transactions
Write a solution to find the IDs of the users who visited without making any transactions and
the number of times they made these types of visits.
Return the result table sorted in any order.
The result format is in the following example.
Example 1:
Input:
Visits
+----------+-------------+
| visit_id | customer_id |
+----------+-------------+
|1 | 23 |
|2 |9 |
|4 | 30 |
|5 | 54 |
|6 | 96 |
|7 | 54 |
|8 | 54 |
+----------+-------------+
Transactions
+----------------+----------+--------+
| transaction_id | visit_id | amount |
+----------------+----------+--------+
|2 |5 | 310 |
|3 |5 | 300 |
|9 |5 | 200 |
| 12 |1 | 910 |
| 13 |2 | 970 |
+----------------+----------+--------+
Output:
+-------------+----------------+
| customer_id | count_no_trans |
+-------------+----------------+
| 54 |2 |
| 30 |1 |
| 96 |1 |
+-------------+----------------+
Explanation:
Customer with id = 23 visited the mall once and made one transaction during the visit with id =
12.
Customer with id = 9 visited the mall once and made one transaction during the visit with id =
13.
Customer with id = 30 visited the mall once and did not make any transactions.
Customer with id = 54 visited the mall three times. During 2 visits they did not make any
transactions, and during one visit they made 3 transactions.
Customer with id = 96 visited the mall once and did not make any transactions.
As we can see, users with IDs 30 and 96 visited the mall one time without making any
transactions. Also, user 54 visited the mall twice and did not make any transactions.
select distinct customer_id , count(customer_id) as count_no_trans
from Visits v
where v.visit_id not in (
select visit_id from Transactions
)
group by v.customer_id
1661. Average Time of Process per Machine
There is a factory website that has several machines each running the same number of
processes. Write a solution to find the average time each machine takes to complete a process.
The time to complete a process is the 'end' timestamp minus the 'start' timestamp. The average
time is calculated by the total time to complete every process on the machine divided by the
number of processes that were run.
The resulting table should have the machine_id along with the average
time as processing_time, which should be rounded to 3 decimal places.
Return the result table in any order.
The result format is in the following example.
Explanation:
There are 3 machines running 2 processes each.
Machine 0's average time is ((1.520 - 0.712) + (4.120 - 3.140)) / 2 = 0.894
Machine 1's average time is ((1.550 - 0.550) + (1.420 - 0.430)) / 2 = 0.995
Machine 2's average time is ((4.512 - 4.100) + (5.000 - 2.500)) / 2 = 1.456
select a.machine_id , round ( avg([Link] - [Link])::numeric , 3) as
processing_time
from Activity a join Activity b on
a.machine_id = b.machine_id and
a.process_id = b.process_id and
a.activity_type = 'start' and b.activity_type = 'end'
group by a.machine_id ;
1667. Fix Names in a Table
Write a solution to fix the names so that only the first character is uppercase and the rest are
lowercase.
Return the result table ordered by user_id.
The result format is in the following example.
select user_id , concat(upper(substring(name,1,1)) , lower(substring(name from 2))) as name
from Users
order by user_id ;
1693. Daily Leads and Partners
For each date_id and make_name, find the number of distinct lead_id's
and distinct partner_id's.
Return the result table in any order.
The result format is in the following example.
select date_id , make_name , count(distinct lead_id ) as unique_leads , count(distinct
partner_id ) as unique_partners
from DailySales
group by date_id , make_name
1731. The Number of Employees Which Report to Each Employee
For this problem, we will consider a manager an employee who has at least 1 other employee
reporting to them.
Write a solution to report the ids and the names of all managers, the number of employees
who report directly to them, and the average age of the reports rounded to the nearest integer.
Return the result table ordered by employee_id.
The result format is in the following example.
select m.employee_id , [Link] , count(e.reports_to) as reports_count , round(avg([Link]) , 0)
as average_age
from Employees e join Employees m on m.employee_id = e.reports_to
group by m.employee_id , [Link]
order by m.employee_id