1.Извештај за сите теретани со просечна оцена поголема од 3.
select users.email, business.address, avg(feedback.rating) as avg_rating from business join users on users.id = business.user_id join feedback on business_id = business.id group by users.email, business.address having avg(feedback.rating) > 3 order by avg_rating desc
2. Извештај за потрошувачката на клиентите преку претплата или гостинско плаќање. (subscription/guestpay)
select users.email, sum(subscription.price) subscription_cost, sum(guest_pay.amount) guest_pay_cost from client join users on client.user_id = users.id left join "subscription" on client_id = client.id left join guest_pay on guest_pay.client_id = client.id group by email having sum(subscription.price) > 0 OR sum(guest_pay.amount) > 0;
3.Извештај за колку корисници го користат гостинското плаќање и немаат претплата.
select count(client.id) from client left join "subscription" on client_id = client.id join guest_pay gp on gp.client_id = client.id where subscription.id is null
Last modified
4 months ago
Last modified on 01/10/24 22:46:26
Note:
See TracWiki
for help on using the wiki.