Complex query mysql

Transact-SQL
1

Hi, im new here. i want to ask about queries using mysql that i cant accomplished. there are 3 tables in the screenshot (sorry for bad quality cause i just can upload 1 image).

Tables:

  1. user : sellid, buyid, register, negara
  2. impress : sellid, total_clicks, impressions, total_views
  3. order : sellid, orderid, buyid, itemid, gmv, time

the questions :
1.search number of customer (buyid) of each country(negara) that purchased item with even and odd itemid (integer) number
2. search number of order/views and clicks/impressions of each sellid

Thankyou

Merged_document
Merged_document409×1670 225 KB

2

if you don't mind could you please upload excel file
or use
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);

INSERT INTO table_name ( column1 , column2 , column3 , ...)
VALUES ( value1 , value2 , value3 , ...);

for all the tables. Once I have data I will try and help you out.

1 Like
3

Welcome

Is this some homework or is this actual work relate? What have you tried?

4

i can't reply with link or a file, and i can't type more than 32000 characters for the syntax, what should i do?

5

Hi, this is my test assignment (not work relate) that i've finished it and no longer valid since last week. But i just want to figure it out because i can't solve in that time until now and i need other perspective about the questions and answers

here i've tried for number 1:

SELECT q.negara, COUNT(q.buyid) AS total_buyer
FROM (SELECT u.buyid, u.negara, COUNT(o.itemid) even
FROM user u
INNER JOIN order o using(buyid)
WHERE itemid%2=0
GROUP BY u.negara, u.buyid
ORDER BY u.negara) q

INNER JOIN

(SELECT u.buyid, u.negara, COUNT(o.itemid) odd
FROM user u
INNER JOIN order o using(buyid)
WHERE itemid%2 = 1
GROUP BY u.negara, u.buyid
ORDER BY u.negara) x

ON q.buyid = x.buyid
GROUP BY q.negara;

6

and for number 2 :

SELECT p.sellid, q.total_order, q.total_order/p.item_views AS order_per_view, p.total_clicks/p.impressions AS click_per_impressions
FROM per impress p
INNER JOIN
(SELECT sellid, COUNT(orderid) total_order
FROM order
GROUP BY sellid
ORDER BY sellid) q
ON p.sellid = q.sellid
GROUP BY p.sellid
ORDER BY p.sellid;