41 lines
950 B
SQL
41 lines
950 B
SQL
-- Frequent Complaint Passengers
|
|
SELECT
|
|
passengers.id AS passenger_id,
|
|
passengers.first_name,
|
|
passengers.last_name,
|
|
passengers.phone,
|
|
COUNT(`feedBack`.id) AS complaint_count
|
|
FROM
|
|
passengers
|
|
JOIN `feedBack` ON passengers.id = `feedBack`.`passengerId`
|
|
GROUP BY
|
|
passengers.id
|
|
ORDER BY
|
|
complaint_count
|
|
DESC
|
|
LIMIT 10;
|
|
--==========
|
|
-- to get all driver payment to pay to them
|
|
SELECT
|
|
p.driverID,
|
|
COALESCE(SUM(p.amount), 0) AS total_amount,
|
|
COALESCE(SUM(p.amount), 0) + COALESCE(pd.total_points, 0) AS diff
|
|
FROM
|
|
payments p
|
|
JOIN (
|
|
SELECT
|
|
driverID,
|
|
SUM(amount) AS total_points
|
|
FROM
|
|
paymentsDriverPoints
|
|
WHERE
|
|
payment_method = 'fromBudgetToPoints'
|
|
GROUP BY
|
|
driverID
|
|
) pd ON p.driverID = pd.driverID
|
|
WHERE
|
|
p.isGiven = 'waiting'
|
|
AND p.payment_method IN ('visa-in', 'visa', 'visaRide', 'TransferFrom', 'payout', 'TransferTo')
|
|
GROUP BY
|
|
p.driverID;
|