SQL View Creation: Syntax Error and Potential Optimization

The view 'table2' seems to have a syntax error. The DATE function should have quotes around the date values. The correct syntax for the view would be:

CREATE VIEW table2 AS ( SELECT * FROM transfer WHERE count(amount) IN ( SELECT count(amount) FROM transfer WHERE trade_type = '转账' AND (DATE(trade_time) BETWEEN DATE('2019-09-30') AND DATE('2019-07-01')) GROUP BY start_account HAVING count(amount) >= 500000 ) );

Note that the condition in the WHERE clause is checking if the count of amount is in the set of counts returned by the subquery. This may not be what you intended, as it will only return transfers with a count of amount that matches one of the counts returned by the subquery. If you want to filter transfers based on the start_account and amount, you may want to use a JOIN instead.