代写data、代做SQL编程设计、代写SQL设计

日期：2020-03-13 09:57

The Confident Cannabis marketing team has launched a small campaign for social media users to vote between two products of the same category. The polls are set up on multiple instagram stories, which will be referred to as rounds.

You are given two tables, PRODUCTS and ROUNDS, with the following structure:

Each record in the table PRODUCTS represents a single product. The column product_id contains the ID of each product. The column category_id contains the ID of the category that each product belongs to.

Each record in the table ROUNDS represents a single round in the category stage. The column first_product (second_product) contains the ID of the first product (second product) in each round. The column first_vote (second_vote) contains the number of votes for the first product (second product) in each round. You may assume that, in each round, products belong to the same category.

You would like to compute the winner in each category. The winner in each category is the product who scored the maximum total number of votes within the category. If there is more than one such product, the winner is the one with the lowest ID.

Write an SQL query that returns a table containing the winner of each category. Each record should contain the ID of the category and the ID of the winner in this category. Records should be ordered by increasing ID number of the category.

Assume that:

●categories are numbered with consecutive integers beginning from 1;

●every product from table rounds occurs in table products;

●in each round products belong to the same category;

●Vote is a value between 0 and 1000000;

Please provide a file with your SQL code, and reply in the email body with what the final answer should be.

Select cate.Name as category_id, pro.Name as product_id, pro.Vote

From category cate, product pro

Where cate.category_id=cate.Id

And pro.vote=(select max(vote) from category e2 where e2.category=cate.Id)