How to write a SQL query for a database?

Question

SQL

Job - /* There are 3 tables.
It is necessary to write a query that will select a list of sites (site) that have more than three active (status=1) blocks (site_area).

The selection should contain four fields: site id, site url, total number of impressions and total income on this site (from all its blocks).

Table data - # list of sites
CREATE TABLE IF NOT EXISTS `site` (
`site_id` INT(12) UNSIGNED NOT NULL,
`url` VARCHAR(255) NOT NULL,
`user_id` INT(12) UNSIGNED NOT NULL,
`status` INT(12) UNSIGNED NOT NULL,
PRIMARY KEY (`site_id`)
)
DEFAULT CHARSET = utf8;
INSERT INTO `site` (`site_id`, `url`, `user_id`, `status`) VALUES
('1025', 'site1.com', '275', 1),
('1026', 'test_site.net', '278', 0),
('1027', 'site17.net', '275', 1),
('1028', 'test_test_site.fr', '270' , one);

# list of blocks
CREATE TABLE IF NOT EXISTS `site_area` (
`site_area_id` INT(12) UNSIGNED NOT NULL,
`name` VARCHAR(255) NOT NULL,
`site_id` INT(12) UNSIGNED NOT NULL,
`status` INT(12 ) UNSIGNED NOT NULL,
`size` VARCHAR(255) NOT NULL,
PRIMARY KEY (`site_area_id`)
)
DEFAULT CHARSET = utf8;
INSERT INTO `site_area` (`site_area_id`, `site_id`, `name`, `status`, `size`) VALUES
('628', '527', 'sidebar_1', 1, '160x600'),
('673', '1025', 'sidebar_4', 1, '300x600'),
('674', '1025', 'sidebar_5', 1, '300x600'),
('676', '1025', ' sidebar_6', 1, '300x600'),
('677', '1025', 'sidebar_7', 0, '300x600'),
('670', '1026', 'sidebar_1', 1, '300x600'),
('671', '1026', 'sidebar_2', 0, '300x250'),
('675', '1026', 'sidebar_3', 0, '300x250');

# statistics
CREATE TABLE IF NOT EXISTS `npm_site_area_stat_cache` (
`date` DATE NOT NULL,
`site_area_id` INT(12) UNSIGNED NOT NULL,
INSERT INTO `npm_site_area_stat_cache` (`date`, `site_area_id`, `impression_count`, `revenue`) VALUES
('2019-10-20', 628, 57565, 25.10),
('2019-10-22', 628, 61277, 40.25),
('2019-10-22', 629, 1245, 0.95),
('2019-10-22', 673, 755, 0.05),
('2019-10-22', 651, 889115, 525.25);

Compiled 1 search query for 3 fields -
SELECT
Site_id, url,
COUNT(user_id)
FROM site
GROUP BY site_id, url;

Then I made a query to find the total revenue

SELECT
site_area_id, revenue,
COUNT(revenue)
FROM npm_site_area_stat_cache
GROUP BY site_area_id, revenue;

I don’t understand how to make a selection by starus=1 and blok_area>3 (to find all fields for such data in the 2nd table)

Answer 1

Try like this:

SELECT
    Site_id, url,
    COUNT(DISTINCT site_area.site_area_id) active_blocks,
    SUM(impression_count) impression_count,
    SUM(revenue) revenue
FROM site
JOIN site_area USING(site_id)
LEFT JOIN npm_site_area_stat_cache USING(site_area_id)
WHERE site_area.status = 1
GROUP BY site_id, url
HAVING COUNT(DISTINCT site_area.site_area_id) > 3;

MySQL query test

Answer 2

SELECT
site_area_id, site_id,
COUNT(site_id)
FROM site_area
WHERE status=1
GROUP BY site_area_id, site_id;
For 2 tables I did, but I don’t understand how to sort by "more than 3 active blocks"