How to count the number of records for a certain period?

Question

Working with date/time

Имеется таблица: actions
id | user_id | type | created_at
В поле type может хранится одно из следующих значений add или remove. Необходимо сделать выборку за определённый интервал (7 дней), с выводом количества add и remove.
Вывод должен быть таким:
2016-09-09 | 10 | 15
2016-09-08 | 23 | 45
Где:
1. Дата
2. Количество записей у которых type = add и created_at равен дате.
3. Количество записей у которых type = remove и created_at равен дате.
Второй вопрос, какой лучше выбрать тип для поля created_at и какие необходимо указать ключи? На всём проекте стоит int(11). Количество записей в таблице может быть 1 000 - 10 000 000.
Now the created_at field has int (11) and such a query has been compiled, which, with thousands of records, takes about 4-5 seconds due to date conversion: DATE(FROM_UNIXTIME(`created_at`)) = `data`

SELECT
          	    DATE(FROM_UNIXTIME(`created_at`)) AS `data`,
                (
                    SELECT
        	            COUNT(`id`)
                    FROM
        	            `actions`
                    WHERE
        	            `user_id` = 1 AND
        	            `type` = 'add' AND
        	            DATE(FROM_UNIXTIME(`created_at`)) = `data`
                ) AS `add`,
                (
                    SELECT
        	            COUNT(`id`)
                    FROM
        	            `actions`
                    WHERE
                      `user_id` = 1 AND
        	            `type` = 'remove' AND
        	            DATE(FROM_UNIXTIME(`created_at`)) = `data`
                )  AS `remove`
            FROM
              `actions`
            WHERE
            `created_at` > UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY))
            GROUP BY `data`

Answer 1

select date(`created_at`) as create_date, sum(if(type = 'new', 1,0)) as new_count, sum(if(type = remove, 1,0)) as rm_count
from actions 
where user_id = 1 and created_at > DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
group by create_date

Well, if you often need to do such an aggregation - make a table with data previously aggregated by day.

Answer 2

1. Extra requests. GROUP BY is needed to group records for a COUNT type function.

SELECT
  DATE(FROM_UNIXTIME(`created_at`))     AS "data",
  COUNT(IF(`type` = "add", 1, NULL))    AS "add",
  COUNT(IF(`type` = "remove", 1, NULL)) AS "remove"
FROM
  `actions`
WHERE
  `created_at` > UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY))
GROUP BY data

select the sampling condition yourself, what period do you need

Answer 3

I wrote in two minutes, so I could have made mistakes ...
I slightly corrected your request:

SELECT DATA,
       SUM(CASE WHEN USER_ID = 1 AND TYPE = 'add' 
            THEN 1 ELSE 0 END) AS ADD,
       SUM(CASE WHEN USER_ID = 1 AND TYPE = 'remove'  
            THEN 1 ELSE 0 END) AS REMOVE
  FROM ACTIONS
 WHERE CREATED_AT > UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY))
 GROUP BY DATA