I'm not sure why distinct is needed in the count. If the table is defined as in your post:
CREATE TABLE EMPLOYEE
( EMPLOYEE_ID VARCHAR
, TITLE VARCHAR(30)
, DEPARTMENT VARCHAR(30)
, ACTIVE BOOLEAN
, LOCATION VARCHAR
, EFFECTIVE_DATE DATE
);
then an employee can exist several times each data:
INSERT INTO EMPLOYEE
(EMPLOYEE_ID,TITLE,DEPARTMENT,ACTIVE,LOCATION,EFFECTIVE_DATE)
VALUES ('Alice','Agnet','AD',TRUE,'APAC','2018-11-27')
, ('Alice','Agnet','AD',TRUE,'APAC','2018-11-27')
Your query won't capture that. Neither will it handle a situation where EFFECTIVE_DATE is null:
INSERT INTO EMPLOYEE
(EMPLOYEE_ID)
VALUES ('Alice'),('Alice'),('Alice');
If the table is defined in a more sane way:
CREATE TABLE EMPLOYEE
( EMPLOYEE_ID VARCHAR NOT NULL
, TITLE VARCHAR(30) NOT NULL
, DEPARTMENT VARCHAR(30) NOT NULL
, ACTIVE BOOLEAN NOT NULL
, LOCATION VARCHAR NOT NULL
, EFFECTIVE_DATE DATE NOT NULL
, PRIMARY KEY (EMPLOYEE_ID, EFFECTIVE_DATE)
);
We know for sure that an employee can only change their profile once a day. It is then not necessary to count distinct dates (you don't have to count dates at all):
SELECT employee_id
,COUNT(*) as COUNT_PROFILE_CHANGE
FROM Employee
GROUP BY employee_id
HAVING COUNT(*) >= 2
ORDER BY 2 DESC
,1 DESC
RDBMS
instead. You should also include NOT NULL constraints and keys for your table