Help with SELECT for the Acuity exam

Asked

Viewed 117 times

-7

I need help with a college test. I’m new in SQL, and I need to do well on this test. I wonder if someone could help me form this SELECT correctly?

I don’t even know where to begin, so I really appreciate any help. I know I must study to reach the answers alone, but the delivery is until tonight 21/02.

We are the company X and our sale is focused on bags and shoes diversified.

We want to make a campaign to offer a Scarpin to all those who bought on category "Sandal" 30 days ago with average ticket of R$150.00 on your last purchase.

Make a query in the database using language SQL Query that meets the established criteria. Consider the "email" field as the primary key. 

Tabela 1  
tb_orders

Campos:

id_pedido
Email
Nome
Valor total
Quantidade de itens
data_compra


Tabela 2  
tb_itens_orders

Campos:

id_pedido
nome_produto
sku_produto
categoria
preco
sql select

  • Forgive me, but it lacks a little in your question to the site... If possible read this here and it would be great if you could apply ;)

  • "Exactly 30 days ago", is that right? Who bought the 29 or 31 days does not enter? What comes to be "average ticket of R$150,00 on your last purchase", average than?

2 answers

2


I think it’ll look something like this, but I can’t test.

SELECT * FROM tb_orders as tor, tb_itens_orders as ti 
WHERE tor.id_pedido = ti.idpedido 
AND
tor.data_compra BETWEEN NOW() AND DATE_SUB(NOW(), INTERVAL 30 DAY) 
AND
ti.nome_produto = 'Sandalia'

  • Thank you, my friend, from my heart.

2

SELECT   o.email, 
         AVG(o.valor_total / o.quantidade_de_itens) ticket_medio
FROM     tbl_orders o
JOIN     tbl_itens_orders i ON i.id_pedido = o.id_pedido
WHERE    i.categoria = 'Sandália' 
AND      o.data_compra = '2019-01-22' --considerando hoje como 21/fev/2019
GROUP BY o.email
HAVING   ticket_medio >= 150.00

Browser other questions tagged sql select

You are not signed in. Login or sign up in order to post.